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
August 2011
(for SAS and WPS)

You have created a list of values in a macro variable separated by a space, but question is, how many values do I have? Lets look at the following example:

   *Lets make our data;
   data _idnum0;
      infile cards;
      input idnum $4;
   cards;
   0001
   0002
   0003
   0004
   0005
   0006
   ;
   run;

   *Create a macro variable of the unique IDs separated by a space, using SQL;
   proc sql noprint;
      select distinct idnum
         into :idlist separated by ' '
         from _idnum0;
      quit;
   run;

   *Count the number of IDs in the list;
   %let _count=%sysfunc(countw(&idlist));

   *Put out number of IDs in the SAS LOG;
   %put There are &_count IDs in the macro variable IDLIST;
   run;

When the code is run the following message will appear in the SAS LOG:

   There are 6 IDs in the macro variable IDLIST

Which is the number of distinct IDs in the data.

The question you may be asking is why use the DISTINCT keyword in the SQL? It is true that in this case the IDs are unique so the DISTINCT keyword is not needed, but if the list was not unique and you were wanting to do some BY processing, the DISTINCT will create that unique list, as the following example will show:

   *Our data;
   data _idcomments0;
      attrib idnum length=$4
             date length=$10 label='Date'
             comment length=$50 label='Comment';
      infile cards;
      input idnum $ date $ comment & $;
   cards;
   0001 2011-01-15 Payment late due to hospitalization.
   0001 2011-01-19 Came in to discuss late payment.
   0002 2011-01-14 Made note to review complete file in February.
   0003 2011-01-17 Made visit as contents of property was destroyed thru flood damage.
   ;
   run;

   *Make unique list of subjects;
   proc sql noprint;
      select distinct idnum
         into :idlist separated by ' '
         from _idcomments0;
      quit;
   run;

   *Count the number of IDs in the list and put number out to SAS LOG;
   %let _count=%sysfunc(countw(&idlist));
   %put There are &_count IDs in the macro variable IDLIST;
   run;

   *Make sure dataset _IDCOMMENTS0 is in ID/DATE order;
   proc sort data=_idcomments0;
      by idnum date;
   run;

   *Use a macro to print the comments;
   %macro tmp;
      title1 "We Always Pay Up Insurance";
      title2 "Listing of Comments";
      %let i=1;  *Internal counter;
      %do %while(%scan(&idlist,&i," ") ne );  *DO loop while IDs exist in macro variable;
         title3 "Customer ID: %scan(&idlist,&i)";  *Set title as ID;
         proc print data=_idcomments0 label noobs;  *PROC PRINT call;
            var date comment;
            where idnum="%scan(&idlist,&i)";  *Subset data by ID;
         run;
         %let i=%eval(&i+1);  *Increment internal counter;
      %end;
   %mend tmp;
   %tmp;
   run;

In the example, the number of distinct IDNUM values is put in a macro variable and a note of the number of unique IDs put out into the SAS LOG -- this is the expected number of outputs we are expecting. In the macro we are going through each IDNUM value in the list and using PROC PRINT to output the comments for that ID. As always, take this code and try it on your version of SAS (a word of warning, the SYSFUNC call will only work on SAS versions 9.1 and above or WPS versions 2.5 and above -- if you have another version, ask me).

This could be better done with a PROC PRINT and a BY statement, or even using the CALL EXECUTE call, but the use of the macro this way shows a way that you could use a macro loop in more complex situations, for example a PROC REPORT with a PROC GCHART call by each center, on the one page.

Hope this is useful. See you in September.

________________________________
Updated August 5, 2011