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 · Accepted Answer · 2017-10-05 17:12:05Z

4

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

answered Oct 5, 2017 at 17:12

Robert Penridge

8,5233 gold badges41 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AwfulPersimmon Over a year ago

Thank you for the comprehensive response. I'm new to SAS macros, so I look forward to playing around with this.

Collectives™ on Stack Overflow

SAS: Create Variable from Proc SQL to use in Macro

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related