SAS: Create Variable from Proc SQL to use in Macro

Question

I want to count the number of unique items in a variable (call it "categories") then use that count to set the number of iterations in a SAS macro (i.e., I'd rather not hard code the number of iterations).

I can get a count like this:

proc sql;
        select count(*)
        from (select DISTINCT categories from myData);
quit;

I can run a macro like this:

%macro superFreq;
    %do i=1 %to &iterationVariable;
        Proc freq data=myData;
        table var&i / out=var&i||freq;
    run;
%mend superFreq;
%superFreq

I want to know how to get the count into the iteration variable so that the macro iterates as many times as there are unique values in the variable "categories".

Sorry if this is confusing. Happy to clarify if need be. Thanks in advance.

Robert Penridge Robert Penridge · Accepted Answer · 2017-10-05T17:12:05

You can achieve this by using the into clause in proc sql:

proc sql noprint;
  select max(age), 
         max(height), 
         max(weight) 
  into :max_age, 
       :max_height, 
       :max_weight 
  from sashelp.class;
quit;

%put &=max_age &=max_height &=max_weight;

Result:

MAX_AGE=      16 MAX_HEIGHT=      72 MAX_WEIGHT=     150

You can also select a list of results into a macro variable by combining the into clause with the separated by clause:

proc sql noprint;
  select name into :list_of_names separated by ' ' from sashelp.class;
quit;

%put &=list_of_names;

Result:

LIST_OF_NAMES=Alfred Alice Barbara Carol Henry James Jane Janet Jeffrey John Joyce Judy Louise Mary Philip Robert Ronald Thomas

William

SAS: Create Variable from Proc SQL to use in Macro

1 Answers