Solved: Re: looping through variables using %mend macro function

MART1 · Posted 03-02-2021 11:37 AM

Hello

I’m using the %mend macro function to repeat the same PROC SQL for several variables in a dataset.

This works well but I’d like to be able to go through the variables automatically, rather than having to list them all – in the below dummy example I only have 3 variables (X, Y, Z), but in the real dataset I have many more, and some can be added or removed.

Looking online I seem to get close to what I need, but can’t find the right code.

Many thanks

/*create a dummy dataset */

PROC SQL;

   CREATE TABLE WORK.SOURCE AS
   SELECT ID, X, Y, Z
      FROM SASHELP.BURROWS
      WHERE ID BETWEEN 100 AND 150;
QUIT;


/*create an empty table that will be populated with the results from the macro */
PROC SQL;
     CREATE TABLE WORK.OUTPUT
           (
           Variable Char (1),
           Count Numeric (12),
           Sum Numeric (12)
           );
QUIT;

%macro makereport(Var=);

PROC SQL;
CREATE TABLE TEMP AS
SELECT  "&Var" AS VARIABLE, COUNT(&Var) as COUNT, SUM(&Var) AS SUM
FROM WORK.SOURCE;
QUIT;

/*append the result of each pass to the new table */
PROC APPEND BASE=WORK.OUTPUT DATA=WORK.TEMP FORCE;
RUN;

%mend makereport;
/*here I must list all the variables, but I like to avoid this*/

%makereport(Var=X)
%makereport(Var=Y)
%makereport(Var=Z)

Reeza · Posted 03-02-2021 12:44 PM

%MEND ends a macro, that's all.

You want to use CALL EXECUTE.

First create a list of the variables - either manually or query sashelp.vcolumn which lists all the variables in a data set.

Then use a data _null_ step to run the macro for each variable.

The last part of this tutorial shows how that can be done.

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

*get list of variables;
proc sql;
create table var_list as
select name
from sashelp.vcolumns
where libname='SASHELP'
and memname = 'CLASS';
quit;

data _null_;
set var_list;
*builds string to execute macro;
str = catt('%makereport(Var=', name, ');');
*execute macro;
call execute(str);

run;

Overall, I would have assumed a PROC MEANS would work much easier, avoids macros and loops entirely and provides the same results.

ods select none;
proc means data=source N SUM noprint;
ods output summary=want;
var x y z;
run;
ods select all;

@MART1 wrote:

Hello

I’m using the %mend macro function to repeat the same PROC SQL for several variables in a dataset.

This works well but I’d like to be able to go through the variables automatically, rather than having to list them all – in the below dummy example I only have 3 variables (X, Y, Z), but in the real dataset I have many more, and some can be added or removed.

Looking online I seem to get close to what I need, but can’t find the right code.

Many thanks
/*create a dummy dataset */

PROC SQL;

   CREATE TABLE WORK.SOURCE AS
   SELECT ID, X, Y, Z
      FROM SASHELP.BURROWS
      WHERE ID BETWEEN 100 AND 150;
QUIT;


/*create an empty table that will be populated with the results from the macro */
PROC SQL;
     CREATE TABLE WORK.OUTPUT
           (
           Variable Char (1),
           Count Numeric (12),
           Sum Numeric (12)
           );
QUIT;

%macro makereport(Var=);

PROC SQL;
CREATE TABLE TEMP AS
SELECT  "&Var" AS VARIABLE, COUNT(&Var) as COUNT, SUM(&Var) AS SUM
FROM WORK.SOURCE;
QUIT;

/*append the result of each pass to the new table */
PROC APPEND BASE=WORK.OUTPUT DATA=WORK.TEMP FORCE;
RUN;

%mend makereport;
/*here I must list all the variables, but I like to avoid this*/

%makereport(Var=X)
%makereport(Var=Y)
%makereport(Var=Z)

View solution in original post

mklangley · Posted 03-02-2021 11:48 AM

Thank you for providing clear, run-able code.
You could transpose your SOURCE dataset, then do a summary with a GROUP BY. Then you wouldn't need a macro for looping:

PROC SQL;
   CREATE TABLE WORK.SOURCE AS
   SELECT ID, X, Y, Z
      FROM SASHELP.BURROWS
      WHERE ID BETWEEN 100 AND 150;
QUIT;

proc transpose data=source
    out=source_transposed
    name=variable
    prefix=amt;
    by id;
run;

proc sql;
    create table output as
    select variable
          ,count(*) as count
          ,sum(amt1) as sum
    from source_transposed
    group by variable
    ;
quit;

MART1 · Posted 03-02-2021 12:29 PM

Thanks @mklangley

I did try PROC TRANSPOSE, however my table has about 150 variables and a few million observations, so I always get a "Not enough storage is available" error message.

thanks

yabwon · Posted 03-02-2021 11:49 AM

You can try the BasePlus package and the %getVars() macro and the macroArray package and %do_over() macro:

https://github.com/yabwon/SAS_PACKAGES

https://github.com/yabwon/SAS_PACKAGES/blob/main/packages/baseplus.md

https://github.com/yabwon/SAS_PACKAGES/blob/main/packages/macroarray.md

filename packages "%sysfunc(pathname(work))"; /* setup temporary directory for packages in the WORK */
filename SPFinit url "https://raw.githubusercontent.com/yabwon/SAS_PACKAGES/main/SPF/SPFinit.sas";
%include SPFinit; /* enable the framework */

%installPackage(macroArray BasePlus)  /* install the package */
%loadPackageS(macroArray, BasePlus)    /* load the package content into the SAS session */


/*create a dummy dataset */

PROC SQL;

   CREATE TABLE WORK.SOURCE AS
   SELECT ID, X, Y, Z
      FROM SASHELP.BURROWS
      WHERE ID BETWEEN 100 AND 150;
QUIT;


/*create an empty table that will be populated with the results from the macro */
PROC SQL;
     CREATE TABLE WORK.OUTPUT
           (
           Variable Char (1),
           Count Numeric (12),
           Sum Numeric (12)
           );
QUIT;

%macro makereport(Var=);

PROC SQL;
CREATE TABLE TEMP AS
SELECT  "&Var" AS VARIABLE, COUNT(&Var) as COUNT, SUM(&Var) AS SUM
FROM WORK.SOURCE;
QUIT;

/*append the result of each pass to the new table */
PROC APPEND BASE=WORK.OUTPUT DATA=WORK.TEMP FORCE;
RUN;

%mend makereport;
/*here I must list all the variables, but I like to avoid this*/


%getVars(
   WORK.SOURCE               
  ,mcArray=myVarList 
)

%do_over(
  myVarList 
 ,phrase=%nrstr(%makereport(Var=%myVarList(&_I_.)))
)

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

MART1 · Posted 03-02-2021 12:33 PM

thanks @yabwon

unfortunately the organisation I work for does not allow to install new packages.

Do you know if the same loop can be done using "standard" loops in SAS?

I can't get the right syntax / loops to make it work.

thanks

yabwon · Posted 03-02-2021 12:38 PM

Try this:

/*create a dummy dataset */

PROC SQL;

   CREATE TABLE WORK.SOURCE AS
   SELECT ID, X, Y, Z
      FROM SASHELP.BURROWS
      WHERE ID BETWEEN 100 AND 150;
QUIT;


/*create an empty table that will be populated with the results from the macro */
PROC SQL;
     CREATE TABLE WORK.OUTPUT
           (
           Variable Char (1),
           Count Numeric (12),
           Sum Numeric (12)
           );
QUIT;

%macro makereport(Var=);

PROC SQL;
CREATE TABLE TEMP AS
SELECT  "&Var" AS VARIABLE, COUNT(&Var) as COUNT, SUM(&Var) AS SUM
FROM WORK.SOURCE;
QUIT;

/*append the result of each pass to the new table */
PROC APPEND BASE=WORK.OUTPUT DATA=WORK.TEMP FORCE;
RUN;

%mend makereport;
/*here I must list all the variables, but I like to avoid this*/


data _null_;
  set WORK.SOURCE;
  array _A_ _numeric_;

  do over _A_;
    call execute('%nrstr(%makereport(Var=' !! vname(_A_) !! '))') ;
  end;

  stop;
run;

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation

MART1 · Posted 03-03-2021 05:46 AM

thanks @yabwon - like Reeza' it's what I needed.

thanks for your help

ballardw · Posted 03-02-2021 12:40 PM

You really want to look at this example:

proc means data=work.source stackodsoutput n sum;
   var _numeric_;
   ods output summary= work.example;
run;

Result:

                                                                                                 

Variable     N             Sum

   ID       51     6375.000000
   X        51         6541483
   Y        51        52602290
   Z        51           27090

If there are variables you do not want in the summary then drop them in a data step option or provide the list of variables wanted on the VAR statement. The Means procedure unlike Proc SQL will allow any of the forms of variable lists and indeed combinations of the stem: var1-var100 and/ or Thisvar -- thatvar on the VAR statement.

MART1 · Posted 03-03-2021 05:49 AM

Thanks @bballard

I did start with Proc Means - the only issue is I need more starts than the ones showed in the dummy example (percentage, count distinct..) so I figured out Proc SQL would give me more options

thanks

Reeza · Posted 03-02-2021 12:44 PM

%MEND ends a macro, that's all.

You want to use CALL EXECUTE.

First create a list of the variables - either manually or query sashelp.vcolumn which lists all the variables in a data set.

Then use a data _null_ step to run the macro for each variable.

The last part of this tutorial shows how that can be done.

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

*get list of variables;
proc sql;
create table var_list as
select name
from sashelp.vcolumns
where libname='SASHELP'
and memname = 'CLASS';
quit;

data _null_;
set var_list;
*builds string to execute macro;
str = catt('%makereport(Var=', name, ');');
*execute macro;
call execute(str);

run;

Overall, I would have assumed a PROC MEANS would work much easier, avoids macros and loops entirely and provides the same results.

ods select none;
proc means data=source N SUM noprint;
ods output summary=want;
var x y z;
run;
ods select all;

@MART1 wrote:

Hello

I’m using the %mend macro function to repeat the same PROC SQL for several variables in a dataset.

This works well but I’d like to be able to go through the variables automatically, rather than having to list them all – in the below dummy example I only have 3 variables (X, Y, Z), but in the real dataset I have many more, and some can be added or removed.

Looking online I seem to get close to what I need, but can’t find the right code.

Many thanks
/*create a dummy dataset */

PROC SQL;

   CREATE TABLE WORK.SOURCE AS
   SELECT ID, X, Y, Z
      FROM SASHELP.BURROWS
      WHERE ID BETWEEN 100 AND 150;
QUIT;


/*create an empty table that will be populated with the results from the macro */
PROC SQL;
     CREATE TABLE WORK.OUTPUT
           (
           Variable Char (1),
           Count Numeric (12),
           Sum Numeric (12)
           );
QUIT;

%macro makereport(Var=);

PROC SQL;
CREATE TABLE TEMP AS
SELECT  "&Var" AS VARIABLE, COUNT(&Var) as COUNT, SUM(&Var) AS SUM
FROM WORK.SOURCE;
QUIT;

/*append the result of each pass to the new table */
PROC APPEND BASE=WORK.OUTPUT DATA=WORK.TEMP FORCE;
RUN;

%mend makereport;
/*here I must list all the variables, but I like to avoid this*/

%makereport(Var=X)
%makereport(Var=Y)
%makereport(Var=Z)

MART1 · Posted 03-03-2021 05:45 AM

@Reeza exactly what I needed works a treat thank you very much!

ps: I did start using Proc Report, but I need to add more stats that the dummy example I showed (percentage, count distinct ect) so figured it out this was a better solution

thank you very much, and for your tutorial in Github

Ready to join fellow brilliant minds for the SAS Hackathon?

Classroom Training Available!