BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
afiqcjohari
Quartz | Level 8

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].);
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

8 REPLIES 8
Reeza
Super User

Call execute. See the second example in the documentation. 

Kurt_Bremser
Super User

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.

afiqcjohari
Quartz | Level 8
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.
Kurt_Bremser
Super User

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.

afiqcjohari
Quartz | Level 8
Can you elaborate more on the group processing? Is it another feature of SAS?
Kurt_Bremser
Super User

@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;
Astounding
PROC Star

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-pe...

 

 

rogerjdeangelis
Barite | Level 11
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 5033 views
  • 0 likes
  • 5 in conversation