SAS Tip of the Month
Last 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!
Updated October 2, 2013