Return to Homepage Goto the Tip of the Month Archive Other interesting pages ... LinkedIn Profile SAS Cheat Sheet Useful SAS Code Full SAS Example Basic Statistics Contact Information SAS Tip of the Month October 2013 (for SAS and WPS) 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