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)
%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)
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;
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
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
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
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;
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.
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
%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)
@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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: