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