| David Franklin, Blog | |
| 2013 Archive | |
| Back | dfranklinnh@gmail.com |
|
JanuaryThere are a number of ways which a master dataset can be updated from a transaction dataset, but here is an olde and a goodie which I still use occasionally. Here is some data in my MASTER dataset: Stadium Unsold_Seats Last_Update North Side 20,000 31DEC2012 South Side 18,000 31DEC2012 East Side 21,000 31DEC2012 West Side 16,000 31DEC2012 Central 14,000 31DEC2012 For our example, on January 1st East Side and Central locations were open and at the end of the day the tickets unsold were: Stadium Unsold_Seats East Side 19,000 Central 11,000 Now lets update using the following code: proc sort data=MASTER;
by stadium last_update;
data TRANSACTION;
set TRANSACTION;
last_update='01JAN2013'd;
proc sort data=TRANSACTION;
by stadium last_update;
data MASTER;
merge MASTER TRANSACTION;
by stadium last_update;
if last.stadium;
run;
In the TRANSACTION dataset we have put the date of the transaction in the file, then sorted both datasets, then merged by the STADIUM and LAST_UPDATE variables, taking the last STADIUM value into the MASTER dataset. There are a number of variations of this that can be useful, but this is the basic update. Have a safe and happy January. FebruaryWhen we are testing something new, particularly if it is electicial or mechanical, the test equipment may have multiple inputs. Lets say we have tested a device that has five inputs with voltage being recorded at different points on a circuit with the following data: TIMEPOINT V1 V2 V3 V4 V5
0 1.3 1.2 1.3 1.4 1.3
1 1.3 1.2 1.3 1.3 1.3
2 1.3 1.2 1.2 1.2 1.3
3 1.3 1.4 1.3 1.0 1.5
Lets say we want to find if there was +/-20% change from the mean value across the timepoint - we could certainly write a piece of code similar to: pp_mean=mean(v1,v2,v3,v4,v5);
if abs((v1-tp_mean)*100/tp_mean)>20 then
put 'WAR' 'NING: Value V1 > +/-20% of Mean across timepoint ' timepoint;
if abs((v2-tp_mean)*100/tp_mean)>20 then
put 'WAR' 'NING: Value V2 > +/-20% of Mean across timepoint ' timepoint;
if abs((v3-tp_mean)*100/tp_mean)>20 then
put 'WAR' 'NING: Value V3 > +/-20% of Mean across timepoint ' timepoint;
if abs((v4-tp_mean)*100/tp_mean)>20 then
put 'WAR' 'NING: Value V4 > +/-20% of Mean across timepoint ' timepoint;
if abs((v5-tp_mean)*100/tp_mean)>20 then
put 'WAR' 'NING: Value V5 > +/-20% of Mean across timepoint ' timepoint;
If there are a 1000 leads then the code would be unworkable. So enter the topic of arrays. We can define ranges of variables to be arrays, in this case we could use: array av(*) v1-v5; There are some basic statistical functions, mean being of them, which will work on array values, as shown below: tp_mean=mean(of av(*)); Now, instead of writing multiple lines to test each value across the timepoint, we could do this in the following way: length msgtxt $200;
if n(tp_mean) then do; *If mean has been calculated across the timepoint;
do i=1 to dim(av);
if n(av(i)) then do; *Check if value is present to avoid error;
if abs( (av(i)-Tp_mean)*100/ Tp_mean)>20 then do;
msgtxt='WAR'||'NING: Value '||vname(av(i))||
' > +/-20% of Mean across the timepoint '||put(timepoint,3.);
put msgtxt;
end;
end;
end;
end;
Note that this code has two conditional statements, one to check if a mean value has been calculated across the time point, and if there is a value to check against the mean. This code can easily be modified to do 1000 leads, much more easily than the "check each variable" as we started with. Have a safe and happy February. MarchThis month is a short, but powerful tip. The two common ways to comment out SAS code is to use either * SAS Code;or /* SAS Code */ The /**/ method cannot be nested, i.e. a pair of '/**/' cannot be nested inside another '/**/'. One way to get around this is to create a dummy macro that is not envoked within your code, for example: data test1;
set test0;
* Some SAS statements;
run;
%macro SKIP;
* SAS Code;
%mend SKIP;
proc print data=test1;
run;
The code inside the SKIP macro will not be run. Have a safe and happy March. AprilOften I have a number of SAS programs to run so I create a batch file in Windows and then run that batch file. But how do you create that batch file? As nearly always, there is more than one way to do it. Lets first look at a SAS program which reads in a directory listing of SAS programs (those with the extension .SAS) and then creates a batch file with all the programs. The first step of the process is to switch on the option NOXWAIT and set the directory we are interested in running the programs, as shown below: %let dir=%str(directory_being_processed); options NOXWAIT; An example of such a directory_being_processed value could be C:\TUAI\LISTINGS Next step is to create a list of .SAS files and put that in the directory_being_processed directory with the file name of TMP.TXT -- these are in name order, using the option '/o:n' and exclude the directory name in front of the file using the option '/b'. data _null_;
length _txt $200;
_txt='dir /b /o:n "'||strip("&dir")||'\*.sas" > "'||strip("&dir")||'\tmp.txt"';
put _txt=;
call system(_txt);
run;
The following step reads in the names of the SAS programs. data _lst0;
length fn $200;
infile "&dir.\tmp.txt";
input fn;
fn=upcase(FN);
run;
In the above step it is possible to have file name selection clauses after the last statement to exclude any programs that you do not want added to the batch file. The last step creates the batch file, with the name of the file being specified in the FILE statement: data _null_;
file "&dir.\RUNALL.bat";
length logfn txt $200;
set _lst0 end=eof;
if _n_=1 then do;
put "set SASLOC=C:\Program Files\SAS\SAS 9.1";
txt='set PRJLOC='||strip("&dir");
put txt;
txt=scan("&dir",1,"\");
put txt;
put 'CD %PRJLOC%';
put 'DEL /Q *.LOG';
put 'DEL /Q *.LST';
end;
txt='"%SASLOC%\sas.exe" -rsasuser -noicon -nosplash '||
'-nostatuswin -nologo -SYSIN "%PRJLOC%\'||strip(fn)||'"';
put txt;
if eof then put 'EXIT';
run;
It is important to note that the line put "set SASLOC=C:\Program Files\SAS\SAS 9.1"; sets the location of the SAS.EXE file which is different for site, operating system and SAS version -- the SET statement is a DOS command that allows a DOS variable to contain a value. A similar task is done in the next line txt='set PRJLOC='||strip("&dir");
where the location of the directory being processed is being set. The following lines will set the drive where your programs are and location for the programs to run: txt=scan("&dir",1,"\");
put txt;
put 'CD %PRJLOC%';
Lastly, any LOG and LST files in the directory are delected using the DOS DEL command: put 'DEL /Q *.LOG'; put 'DEL /Q *.LST'; Note that as a rule I delete any LOG files before I run a SAS program just in case the (rare) situation arises where the SAS LOG is not replaced during the run of the program. The line txt='"%SASLOC%\sas.exe" -rsasuser -noicon -nosplash '||
'-nostatuswin -nologo -SYSIN "%PRJLOC%\'||strip(fn)||'"';
put txt;
creates the DOS SAS batch file statement that calls each program. Finally, the EXIT command closes the DOS window when the batch file is run. If you are running WPS, the line put "set SASLOC=C:\Program Files\SAS\SAS 9.1"; would be relaced by the line put "set WPSLOC=C:\Program Files\World Programming WPS 3"; (or similar), and the line txt='"%SASLOC%\sas.exe" -rsasuser -noicon -nosplash '||
'-nostatuswin -nologo -SYSIN "%PRJLOC%\'||strip(fn)||'"';
would be replaced by the lines logfn=tranwrd(upcase(fn),".SAS",".log");
txt='"%WPSLOC%\bin\wps" "%PRJLOC%\'||strip(fn)||
'" > "%PRJLOC%\'||strip(logfn)||'"';
Hope this is useful. Have a safe and happy April. MaySometimes we want to add the observation number to a dataset. In the datastep we can easily do data dataset;
set dataset;
obsn=_n_;
run;
However, if you are using SQL, this is not so easy. There is an undocumented function called the MONOTONIC function that will help, as the following code shows: proc sql;
create table dataset as
select monotonic() as N, *
from dataset;
quit;
run;
Have a safe and happy May. JuneHow do you add an observation with no values for all variables on to a dataset with no observations? Lets look at a dataset that is frequently referenced elsewhere called CLASS with the following structure: data class;
attrib name length=$8
sex length=$1
age length=8
height length=8
weight length=8;
delete;
run;
The dataset now has five variables and zero records. Now lets add a blank record. It should be possible to run run the following code which opens the dataset CLASS, sets a value of MISSING for the variable NAME, and then output that record: data class;
set class;
call missing(name);
output;
run;
However, running this code DOES NOT create the blank record. For many, the resolution to this problem is to create a temporary dataset with one of the variables set to missing and then append the temporary dataset to the original dataset, as shown below: data temporary;
length name $8;
call missing(name);
output;
data class;
set class temporary;
run;
This certainly works! Instead of using the SET statement, the MODIFY statement works as the following code demonstrates: data class;
if 0 then modify class;
call missing(name);
output;
run;
This works but it still requires us to write a variable but it needs knowledge of the dataset. Can we rewrite this step so we do not have to know the structure of the dataset? If we change the CALL MISSING statement to call missing(of _all_); so the whole datastep code is data class;
if 0 then modify class;
call missing(of _all_);
output;
run;
Again, this works but uses the MODIFY statement -- is it still possible to use the SET statement to create a blank record? From above, we know putting the SET statement after the DATA statement does not work, but what if we tried data class;
name=compress(" ");
output;
set class;
run;
this works, but again we have to know the structure of the dataset -- one other issue is that the variable NAME is set to a length of 1, not 8 as originally set (the reason for why the variable is set to 1 is that the first statement after the DATA statement sets the amount of characters to 1 by the length of text before the COMPRESS statement). What if we were to remove the second line, ie. have the code as follows: data class;
output;
set class;
run;
On the first pass of the datastep the OUTPUT statement is not actioned but the output dataset is constructed from the SET statement, and it is on the second pass of the datastep that the OUTPUT statement is actioned resulting in a blank record, and with the SET statement not getting any records (there are no records in the dataset CLASS being read in) that the datastep stops -- result a dataset with no records now having a single record with all the variable values as missing! Hope this is useful. See you in July. JulyOne of the most significant software packages to complement SAS/WPS in recent years is R. That is a very bold statement but increasingly statisticians outside the academic environment are using R to do analysis of data, not SAS. Cost is a factor, but the easy availability of specialist packages written by outside users to do tasks is certainly something to take note of, even by a SAS/WPS Programmer. I have heard the arguments saying that SAS/WPS software is checked by quality control people and therefore their statistics are correct but one argument against that claim is that the software to do the statistics is "in a black box" and users do not have access to the source code behind the calculations, unlike R where the source code is available. An example I use is a paper I have presented entitled "Oh Quartile, Where Art Thou?" where the quartile calculation is discussed and compared against different packages, including SAS and R -- each have their own way of doing the calculation, sometimes multiple ways (which one is best, I leave that up to the Statisticians to decide). For the record, SAS has five different ways to calculate the quartile and I would bet most statisticians do not know what the default calculation is without looking up a reference. Anyway, I digress. This month I look at a couple of examples getting data to R from SAS/WPS, doing some calculations (including a quartile that is not available in SAS) and then retrieve that data back into SAS/WPS. We will also have a quick look at an example of producing a graph. First some data: data demog;
infile cards;
input id sex $ age htcm wtkg;
cards;
01 F 41 170 82.0
02 M 23 172 72.8
03 F 59 194 81.6
04 F 24 174 72.3
05 F 37 160 90.4
06 M 38 184 83.2
07 F 30 160 99.8
08 M 39 175 78.3
09 F 24 185 83.7
10 F 64 188 92.3
11 M 42 160 86.8
12 F 37 176 80.6
13 M 34 170 64.3
14 M 22 172 96.8
15 M 19 157 70.6
16 F 19 155 95.1
17 F 52 184 82.2
18 F 26 160 83.6
19 M 54 187 94.9
20 F 30 190 63.1
;
run;
R can't read SAS native datasets, but can read SAS XPORT Transport Files, this the code would be: libname demogxpt XPORT 'c:\temp\demogxpt.xpt';
data demogxpt.demogxpt;
set demog;
run;
One reason for transferring the data via a SAS XPORT Transport File is that numeric precision of the transport file is far better than via a CSV file or most other type of files. Now that we have the SAS XPORT Transport File, we need to write the code needed to run inside R -- this will be first placed in an external file called program.r. Note that our first statistics will be just getting the N, mean, standard deviation, median, minimum and maximum values for each SEX value. *Create PROGRAM.R file;
DATA _null_;
FILE 'c:\temp\program.r' LRECL=1024;
put '#Bring in additional packages needed for analysis';
put 'library(SASxport)';
put 'library(doBy)';
put '#Read in data -- note "/" for directory names, even in Windows';
put 'mydata = read.xport("c:/temp/demogxpt.xpt")';
put '#Result calculation for AGE by SEX';
put 'r1<-summaryBy(AGE~SEX,data=mydata,FUN=function(x){c(N=NROW(x),MA=mean(x),';
put ' SD=sd(x),MD=median(x),MN=min(x),MX=max(x))})';
put 'r1$IDVar <- "AGEYR" #Add analysis variable to first column';
put 'names(r1) <- c("SEX","N","MEAN","STDDEV","MEDIAN","MIN","MAX","IDVar")';
put '#Result calculation for HTCM by SEX';
put 'r2<-summaryBy(HTCM~SEX,data=mydata,FUN=function(x){c(N=NROW(x),MA=mean(x),';
put ' SD=sd(x),MD=median(x),MN=min(x),MX=max(x))})';
put 'r2$IDVar <- "HTCM"';
put 'names(r2) <- c("SEX","N","MEAN","STDDEV","MEDIAN","MIN","MAX","IDVar")';
put '#Result calculation for WTKG by SEX';
put 'r3<-summaryBy(WTKG~SEX,data=mydata,FUN=function(x){c(N=NROW(x),MA=mean(x),';
put ' SD=sd(x),MD=median(x),MN=min(x),MX=max(x))})';
put 'r3$IDVar <- "WTKG"';
put 'names(r3) <- c("SEX","N","MEAN","STDDEV","MEDIAN","MIN","MAX","IDVar")';
put '#Combine the three calculations into one data frame';
put 'r <- rbind(r1,r2,r3)';
put '#Write the results out as a CSV file';
put 'write.csv(r, file = "c:/temp/rdata_demog.csv", row.names=FALSE)';
put 'q() #Exit';
RUN;
Now it is running this PROGRAM.R file using R in batch mode, as seen below: OPTIONS XWAIT XSYNC;
DATA _null_;
LENGTH _str $200;
*Location for R version 3.0.1, calling PROGRAM.R and output a LOG to PROGRAM.LOG;
_str='"C:\Program Files\R\R-3.0.1\bin\r.exe" --no-save --quiet '||
'< "c:\temp\program.r" > "c:\temp\program.log"';
CALL SYSTEM(_str);
RUN;
Running these two datasteps in SAS or WPS create a CSV file with the N, mean, standard deviation, median, minimum and maximum values for each SEX value for age, height and weight. This CSV file can then be read in using a datastep or PROC IMPORT, among others, and processed in SAS/WPS. Yes, we can get the same result using PROC MEANS, PROC SUMMARY or PROC UNIVARITE but it is useful to see want the same can be done in R. It must be noted that while I present a solution here that writes an external file and then has another step to run that code in R, there is a product called Bridge to R for WPS developed by MineQuest Business Analytics that can do this for you. As mentioned previously, R has a large number of specialist packages that help in doing analysis, in this case we have used SASxport to bring the data into an R data frame, and use a doBy package to do analysis by a variable, in this case SEX. Note that comments are preceded by a '#' character. One very important thing to note is that variable and data frame names are case sensitive -- case does matter in R! Now for the case where the statistic cannot be calculated in SAS/WPS, getting the quartiles based on the Hyndman and Fan definition (yes, I have a paper out there called "Oh Quartile, Where Art Thou?" than shows a macro to do it, but we are using R instead for this example). Again we shall use the same data we brought in above: *Create PROGRAM.R file;
DATA _null_;
FILE 'c:\temp\program.r' LRECL=1024;
put '#Bring in additional packages needed for analysis';
put 'library(SASxport)';
put '#Read in data -- note "/" for directory names, even in Windows';
put 'mydata = read.xport("c:/temp/demogxpt.xpt")';
put '#Subset data into Male and Female';
put 'datasexF <- mydata[ which(mydata$SEX=='F'), ]';
put 'datasexM <- mydata[ which(mydata$SEX=='M'), ]';
put '#Female Weight';
put 'r1 <- quantile(datasexF$WTKG, probs = c(0.25, 0.5, 0.75), type=8)';
put 'r1$IDVar <- "WTKG"';
put 'r1$SEX <- "F"';
put '#Male Weight';
put 'r2 <- quantile(datasexM$WTKG, probs = c(0.25, 0.5, 0.75), type=8)';
put 'r2$IDVar <- "WTKG"';
put 'r2$SEX <- "M"';
put '#Combine the two calculations into one data frame';
put 'r <- rbind(r1,r2)';
put '#Write the results out as a CSV file';
put 'write.csv(r, file = "c:/temp/rdata_demog_quantile.csv", row.names=FALSE)';
put 'q() #Exit';
RUN;
If we run this newly created file with the datastep containing the CALL SYSTEM call we get a CSV file called rdata_demog_quantile with the quartile results for the 1st, 2nd and 3rd quartiles (quintile of 0.25 = first quartile, 0.50 = second quartile or median, and 0.75 = third quartile) that we can import this CSV file into SAS/WPS using a datastep or PROC IMPORT, among others. In the two examples given above we have managed to write the output to a CSV file for import into SAS/WPS, however there are occasions when a data frame is not created so it is necessary to capture the output as a text file and process that file inside SAS/WPS. The following R code illustrates this: out<-capture.output(r_code) cat(out,file="c:/temp/out.txt",sep="\n",append=FALSE) #First result, do not append out<-capture.output(more_r_code) cat(out,file="c:/temp/out.txt",sep="\n",append=TRUE) #Second result, do append People who promote R use the ease at which graphs can be created as one of the reasons for why R is better than SAS/WPS -- I am not going to go into that debate as it can be argued for a very long time. What I will do is create a simple graph where the output is in JPG format: #Bring in additional packages needed for analysis
library(SASxport)
#Read in data -- note "/" for directory names, even in Windows
mydata = read.xport("c:/temp/demogxpt.xpt")
jpeg(file='c:/temp/SexWtKg.jpg', width=640, height=480) #Setup output JPEG file
z <- mydata #Copy mydata to z
#Recode SEX variables
attach(z)
z$SEXCAT[SEX=="F"] <- "Female" # Relabel F for Female
z$SEXCAT[SEX=="M"] <- "Male" # Relabel M for Male
detach(z)
#Mean and Median for weight by sex;
m1 <- tapply(z$WTKG, list(z$SEXCAT), mean)
m2 <- tapply(z$WTKG, list(z$SEXCAT), median)
#Combine the two calculations into one data frame
r <- rbind(m1,m2)
#Generate graph
barplot(r,col=c("yellow","light green"),ylim=c(0,150),beside=T,
main="Mean and Median Weights by Gender",ylab="Weight (kg)",xlab="Sex")
legend("topright",c("Mean","Median"),col=c("yellow","light green"),pch=15)
dev.off() #Switch off device for creating graph
Again, we can wrap this in a datastep and call the R code in a datastep as shown previously. For changing the output to a PDF format instead of JPEG, we could change the jpeg(file='c:/temp/SexWtKg.jpg', width=640, height=480) #Setup output JPEG file for pdf(file="C:/temp/SexWtKg.pdf", height=6.5, width=9, paper="USr") #Setup output PDF file This months' tip has only touched the surface of R. Is SAS/WPS better than R? This is a question that I am not prepared to get involved in, but R is certainly a package that complements SAS and WPS -- the two software packages can co-exist together achieving the output that is desired. Hope this is useful. See you in August. AugustGetting data into SAS from an Excel speadsheet is a task that is often carried out by the SAS Programmer. One reason is that Excel is a good package for entering and editing data and does have some audit capabilites. But how do we get the data on the spreadsheet to a SAS dataset? Let first look at some data on a sheet called LongRivers in a file called Rivers.xls located in the directory C:\TEMP: NAME LENGTH_KMS Nile River 6,695 Amazon River 6,516 Yangtze River 6,380 Mississippi-Missouri River System 5,969 Ob-Irtysh Rivers 5,568 Yenisey-Angara-Selenga Rivers 5,550 Huang He (Yellow River) 5,464 Congo River 4,667 Rio de la Plata-Parana 4,500 Mekong River 4,425 As can be seen the structure for this sheet is two columns, no merged cells, and the column names are in row 1. This is as basic as they come but is the general structure for most sheets that hold data. We can certainly save the sheet as a CSV file and import that file, but that is an extra step -- where possible it is best to try and import the data directly using PROC IMPORT and the EXCEL option that is available inside the procedure. The basic syntax for the use of the procedure is: PROC IMPORT DATAFILE="filename" OUT=dataset_name DBMS=EXCEL REPLACE;
SHEET="sheet_name$";
GETNAMES=YES|NO;
RUN;
where
The DBMS=EXCEL option tells SAS that the data coming in is from an Excel spreadsheet, while the REPLACE option is necessary if you want to overwrite an existing dataset (too many imports of data have failed because the programmer assumed any dataset of the same name would be automatically overwrittern when it is not!). The GETNAMES option is a YES or NO value and gives the choice for the user to get the names of the variables on the first row -- if YES, any blanks are converted to '_', and if NO then the variable names become F1, F2, etc. There are other statements that can be used including MIXED, RANGE, SCANTEXT, USEDATA and SCANTIME but these are beyond the example used here -- consult the SAS User Documentation for the use of these statements. With this knowledge, lets import the data from the Excel file: PROC IMPORT DATAFILE="C:\TEMP\Rivers.xls" OUT=longrivers DBMS=EXCEL REPLACE;
SHEET="LongRivers$";
GETNAMES=YES;
RUN;
The data we imported is ready for use. It is possible to do a quick check on the data by running the following: title1 "Listing of Ten Longest Rivers in the World"; PROC PRINT DATA=longrivers; RUN; and we get the following output: Listing of Ten Longest Rivers in the World
LENGTH_
Obs NAME KMS
1 Nile River 6695
2 Amazon River 6516
3 Yangtze River 6380
4 Mississippi-Missouri River System 5969
5 Ob-Irtysh Rivers 5568
6 Yenisey-Angara-Selenga Rivers 5550
7 Huang He (Yellow River) 5464
8 Congo River 4667
9 Rio de la Plata-Parana 4500
10 Mekong River 4425
I hope this has been helpful. See you in September! SeptemberLast month we looked at importing some data from Excel, this month we are going to look at the converse, exporting some data to Excel. From the start, there are a number of ways of doing this but I am just looking at the basic, put the data out to a sheet with variable names in the first row. There are ways to format cells, color code cells or rows, and a number of other things, but that is beyond this tip. First some data: data student;
length name $20 sex $1 age 8;
input name $ sex $ age;
cards;
Alexandra F 15
Bailey F 16
Blake M 18
Caroline F 16
Dominic M 13
Jose M 17
Justin M 18
Melanie F 15
Oliver M 15
Sydney F 14
;
run;
Once we run this dataset you will see that their are three variables and ten observations. How do we get this into Excel? Last month we looked at PROC IMPORT, well there is a related procedure called PROC EXPORT for getting the data out. The basic syntax for the use of the procedure is: PROC EXPORT DATA=dataset_name FILE="filename" DBMS=EXCEL REPLACE;
SHEET="sheet_name";
RUN;
where
The DBMS=EXCEL option tells SAS that the data being created will be in an Excel spreadsheet, while the REPLACE option is necessary if you want to overwrite an existing sheet (too many imports of data have failed because the programmer assumed any dataset of the same name would be automatically overwrittern when it is not!). There are other statements that can be used including MIXED and RANGE, but these are beyond the example used here -- consult the SAS User Documentation for the use of these statements. With this knowledge, lets export the data to the Excel file: PROC EXPORT DATA=student FILE="C:\TEMP\Student.xls" DBMS=EXCEL REPLACE;
SHEET="StudentDemographics";
RUN;
Once this is run, the Excel spreadsheet with the data from the dataset is in the file Student.xls in the tab StudentDemographics. One thing to note using this method, the variable names are sent to the first row of the spreadsheet -- it is not possible to send variable label text using this method. I hope this has been helpful. See you in October! OctoberLast month we created an Excel spreadsheet with a single tab. The question was asked if it was possible to make different tabs for each sex? The short answer is yes, but this month I will show a method that can be adapted for many splits of the data. Lets get our data again: data student;
length name $20 sex $1 age 8;
input name $ sex $ age;
cards;
Alexandra F 15
Bailey F 16
Blake M 18
Caroline F 16
Dominic M 13
Jose M 17
Justin M 18
Melanie F 15
Oliver M 15
Sydney F 14
;
run;
The request is split this by sex, so the first thing to do is get a list of the unique sex values and put these in a macro variable: *Get unique list of SEX values;
proc summary data=student nway;
class sex;
output out=_freq1 (drop=_type_ _freq_);
run;
*Build macro variable list of unique SEX values from last step;
data _null_;
length sexlist $10;
retain sexlist;
set _freq1 end=eof;
if _n_=1 then _sexlist=strip(sex);
else _sexlist=catx(' ',_sexlist,sex);
if eof then call symput('_sexlmac',strip(_sexlist));
run;
I could have used a PROC SORT call rather than a PROC SUMMARY, or I could have done all of this in one step using a PROC SQL call, but I wanted to show here that there are more than one way of getting a list. For those of you asking what the PROC SQL call would look like, see below: proc sql noprint;
select distinct sex
into :_sexlmac separated by ' '
from student;
quit;
run;
Now lets see what the code would look like to put the data into a spreadsheet by SEX value, now what we have put the unique values into a macro variable: %macro tmp;
%do %while(%scan(&_sexlmac,&i) ne );
data subset;
set student;
if sex="%scan(&_sexlmac,&i)";
drop sex;
run;
PROC EXPORT DATA=subset
FILE='C:\TEMP\Student.xls'
DBMS=EXCEL REPLACE;
SHEET="Student_Sex_%scan(&sexlist,&i)";
RUN;
%let i=%eval(&i+1);
%end;
%mend tmp;
%tmp;
run;
If you had already created the spreadsheet from last month, this data will append the two tabs onto that spreadsheet. See you in November! NovemberI was reviewing some SAS code recently and I spotted a mistake that every programmer makes at sometime -- when merging two datasets in a datastep, the BY statement is missing, as the following example demonstrates: data mergeddata;
merge dataset1 dataset2;
run;
If we look at the SAS LOG the following (or similar) will appear: 250 data mergeddata;
251 merge dataset1 dataset2;
252 run;
NOTE: There were 10 observations read from the data set
WORK.DATASET1.
NOTE: There were 9 observations read from the data set
WORK.DATASET2.
NOTE: The data set WORK.MERGEDDATA has 10 observations and 2
variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
There is no indication that there is a potential problem, infact there could be a very serious problem. If a BY statement is missing, the two datasets will be matched by observation number, so the first observation in dataset1 is matched with the first observation in dataset2, and so on. It can be shown best with the following data: Dataset1 Dataset2 mergeddata
Obs name sex name age name sex age
1 Alexandra F Alexandra 15 Alexandra F 15
2 Bailey F Bailey 16 Bailey F 16
3 Blake M Caroline 16 Caroline M 16
4 Caroline F Dominic 13 Dominic F 13
5 Dominic M Jose 17 Jose M 17
6 Jose M Justin 18 Justin M 18
7 Justin M Melanie 15 Melanie M 15
8 Melanie F Oliver 15 Oliver F 15
9 Oliver M Sydney 14 Sydney M 14
10 Sydney F Sydney F .
As can be seen the resulting dataset is worthless at best. There is an option called MERGENOBY that has been around for some time that can issue a warning or error message to the SAS LOG if in a datastep that is merging data with a MERGE statement has no BY statement, and has the following options:
As a matter of course I have the option MERGENOBY=WARN in my programming so that if I forget a BY statement then a WARNING message will appear, you should to. See you in December! DecemberThis month it is a quick look at PROC FREQ and just trying to get what we need. First, lets look at some data: data student;
length name $20 sex $1 age 8 major $20;
input name $ sex $ age major $;
cards;
Alexandra F 15 Science
Bailey F 16 Mathematics
Blake M 18 Art
Caroline F 16 Sports
Dominic M 13 Literature
Jose M 17 Art
Justin M 18 Literature
Melanie F 15 Mathematics
Oliver M 15 Sports
Sydney F 14 Science
;
run;
A request has come in to get a count of the number of students by sex and major, so lets use PROC FREQ with the following call: proc freq data=student;
tables sex*major;
run;
which results in the following output: The FREQ Procedure
Table of sex by major
sex major
Frequency|
Percent |
Row Pct |
Col Pct |Art |Literatu|Mathemat|Science |Sports | Total
| |re |ics | | |
---------+--------+--------+--------+--------+--------+
F | 0 | 0 | 2 | 2 | 1 | 5
| 0.00 | 0.00 | 20.00 | 20.00 | 10.00 | 50.00
| 0.00 | 0.00 | 40.00 | 40.00 | 20.00 |
| 0.00 | 0.00 | 100.00 | 100.00 | 50.00 |
---------+--------+--------+--------+--------+--------+
M | 2 | 2 | 0 | 0 | 1 | 5
| 20.00 | 20.00 | 0.00 | 0.00 | 10.00 | 50.00
| 40.00 | 40.00 | 0.00 | 0.00 | 20.00 |
| 100.00 | 100.00 | 0.00 | 0.00 | 50.00 |
---------+--------+--------+--------+--------+--------+
Total 2 2 2 2 2 10
20.00 20.00 20.00 20.00 20.00 100.00
As can be seen the information displayed includes frequency, and cell, row and column percentages, but all we really want is the frequency! There are serveral options with the table statement that control the statistics produced, including:
Now lets run the the PROC FREQ with some options to control the display: proc freq data=student;
tables sex*major /nopercent norow nocol;
run;
and this results in the following output: The FREQ Procedure
Table of sex by major
sex major
Frequency|Art |Literatu|Mathemat|Science |Sports | Total
| |re |ics | | |
---------+--------+--------+--------+--------+--------+
F | 0 | 0 | 2 | 2 | 1 | 5
---------+--------+--------+--------+--------+--------+
M | 2 | 2 | 0 | 0 | 1 | 5
---------+--------+--------+--------+--------+--------+
Total 2 2 2 2 2 10
This just gives us counts which is what we want! Hope you have a safe and happy December. See you next year! |