topic Re: How to create a list of macro variables using proc SQL? in SAS Programming

How to create a list of macro variables using proc SQL?

afiqcjohari — Thu, 16 Mar 2017 07:47:15 GMT

First I want to get the distinct list of the variable.

Here let's say I have 4 different levels for var.

But what if I have unknown different levels? Is there a way to generate the macro variables on the fly?

proc sql;
   select distinct var into :v1 -:v4 from t;
quit;

Then for each of them, I want to loop over a series of macros.

For example

%macroone(a = &v1.);
%macrotwo(a = &v1.);

Is there such a thing as a macro array where I can loop using the position of the array?

Such as

%macroone(a= &v[1].);

Re: How to create a list of macro variables using proc SQL?

Reeza — Thu, 16 Mar 2017 07:53:54 GMT

Call execute. See the second example in the documentation.

Re: How to create a list of macro variables using proc SQL?

Kurt_Bremser — Thu, 16 Mar 2017 08:00:37 GMT

Instead of storing your distinct values (how can you guarantee there are only four?) in macro variables, store them in a new dataset, and then do a data _null_ step from that, and use call execute to call your macro for every distinct item. That scales indefinitely.

Re: How to create a list of macro variables using proc SQL?

afiqcjohari — Thu, 16 Mar 2017 08:03:32 GMT

Yes, so I thought of calculating the number of distinct values first. Use this number to generate as many macro variables needed.

I'd need the macro variables as a suffix to one of the macro outputs. Not sure whether data_null_step allows the latter.

Re: How to create a list of macro variables using proc SQL?

Astounding — Thu, 16 Mar 2017 08:08:05 GMT

You can put the complete list into a single macro variable (as long as none of the values contain embedded blanks). Then process each item in the list. Here are the basics:

http://blogs.sas.com/content/sastraining/2015/01/30/sas-authors-tip-getting-the-macro-language-to-perform-a-do-loop-over-a-list-of-values/

Re: How to create a list of macro variables using proc SQL?

Kurt_Bremser — Thu, 16 Mar 2017 08:13:58 GMT

Make your macro self-contained, so that it works as a "black box" (no side effects, all parameters appear in the macro definition). Names of output datasets should be derived from the parameters or supplied as a separate parameter.

Then you store all needed parameter values in the dataset and do

data _null_;
set control_dataset;
call execute('%macro_call(param1='!!strip(param1)!!',param2='!!strip(param2)!!');');
run;

Be aware that macro logic will be executed as soon as you place the macro in the queue with call execute (while your data _null_ is still running), but all data and proc steps created from that will only start after the data _null_ has finished. So you should not create new macro variables in data steps for further use within the macro.

And finally, often operations like you seem to have in mind can be handled with by group processing, without the need for macro processing at all.

Re: How to create a list of macro variables using proc SQL?

afiqcjohari — Thu, 16 Mar 2017 08:16:28 GMT

Can you elaborate more on the group processing? Is it another feature of SAS?

Re: How to create a list of macro variables using proc SQL?

Kurt_Bremser — Thu, 16 Mar 2017 08:37:02 GMT

@afiqcjohari wrote:
Can you elaborate more on the group processing? Is it another feature of SAS?

By-group processing is one of THE features of SAS.

Compare this

%macro means(group);

data class;
set sashelp.class;
where sex = "&group";
run;

proc means data=class;
var age;
output out=class_&group;
run;

%mend;

proc sql noprint;
select count(distinct(sex)) into :varcount from sashelp.class;
quit;

%let varcount=%sysfunc(strip(&varcount));

proc sql noprint;
select distinct(sex) into :var1 - :var&varcount from sashelp.class;
quit;

%macro run_all;
%do i = 1 %to &varcount;
%means(&&var&i);
%end;
%mend;

%run_all;

With this:

proc sort data=sashelp.class out=class;
by sex;
run;

proc means data=class;
by sex;
var age;
output out=class_means;
run;

Re: How to create a list of macro variables using proc SQL?

rogerjdeangelis — Thu, 16 Mar 2017 15:18:42 GMT

Without knowing the number of levels of a variable create means output for each level

inspired by
https://goo.gl/8Bf1ap
https://communities.sas.com/t5/Base-SAS-Programming/How-to-create-a-list-of-macro-variables-using-proc-SQL/m-p/341449

HAVE
====

Up to 40 obs from sashelp.class total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

  1    Alfred      M      14     69.0      112.5
  2    Alice       F      13     56.5       84.0
  3    Barbara     F      13     65.3       98.0
  4    Carol       F      14     62.8      102.5
  5    Henry       M      14     63.5      102.5
  6    James       M      12     57.3       83.0
  7    Jane        F      12     59.8       84.5
  8    Janet       F      15     62.5      112.5
  9    Jeffrey     M      13     62.5       84.0
 10    John        M      12     59.0       99.5
 11    Joyce       F      11     51.3       50.5
 12    Judy        F      14     64.3       90.0
 13    Louise      F      12     56.3       77.0
 14    Mary        F      15     66.5      112.0
 15    Philip      M      16     72.0      150.0
 16    Robert      M      12     64.8      128.0
 17    Ronald      M      15     67.0      133.0
 18    Thomas      M      11     57.5       85.0
 19    William     M      15     66.5      112.0

WANT ( proc means listing and  output dataset by levels of variable)
=====================================================================

Up to 40 obs from CLASS_M total obs=5

Obs    _TYPE_    _FREQ_    _STAT_      AGE

 1        0        10       N        10.0000
 2        0        10       MIN      11.0000
 3        0        10       MAX      16.0000
 4        0        10       MEAN     13.4000
 5        0        10       STD       1.6465


Up to 40 obs from CLASS_F total obs=5

Obs    _TYPE_    _FREQ_    _STAT_      AGE

 1        0         9       N         9.0000
 2        0         9       MIN      11.0000
 3        0         9       MAX      15.0000
 4        0         9       MEAN     13.2222
 5        0         9       STD       1.3944

Sex=F
                     Analysis Variable : AGE

 N            Mean         Std Dev         Minimum         Maximum
------------------------------------------------------------------
 9      13.2222222       1.3944334      11.0000000      15.0000000
------------------------------------------------------------------

Sex=F
                     Analysis Variable : AGE

 N            Mean         Std Dev         Minimum         Maximum
------------------------------------------------------------------
10      13.4000000       1.6465452      11.0000000      16.0000000
------------------------------------------------------------------


WORKING CODE
============


      select quote(max(sex)) into :sex separated by ","
         do grp=&sex;
           call symputx('grp',grp);
           dosubl
              proc means data=sashelp.class(where=(sex="&grp"));
              output out=class_&grp.

FULL SOLUTION
=============

proc datasets lib=work kill;
run;quit;

%symdel grp sex;
data _null_;

  if _n_=0 then do;
    %let rc=%sysfunc(dosubl('
      proc sql noprint;
       select quote(max(sex)) into :sex separated by ","
       from sashelp.class group by sex
      ;quit;
    '));
  end;

   do grp=&sex;
      call symputx('grp',grp);
      rc=dosubl('
         proc means data=sashelp.class(where=(sex="&grp"));
           title "Sex=&grp";
           var age;
           output out=class_&grp.;
         run;quit;
      ');
   end;

   stop;
run;quit;


SYMBOLGEN:  Macro variable GRP resolves to F
NOTE: There were 9 observations read from the data set SASHELP.CLASS.
      WHERE sex='F';
NOTE: The data set WORK.CLASS_F has 5 observations and 4 variables.
NOTE: PROCEDURE MEANS used (Total process time):
      real time           0.09 seconds

SYMBOLGEN:  Macro variable GRP resolves to M
NOTE: There were 10 observations read from the data set SASHELP.CLASS.
      WHERE sex='M';
NOTE: The data set WORK.CLASS_M has 5 observations and 4 variables.
NOTE: PROCEDURE MEANS used (Total process time):
      real time           0.06 seconds