Hi, I am trying to create a loop that I can use that will get the min and max value from each column in multiple tables. Can this be done?
See this:
/* create an example table */
data class;
set sashelp.class;
run;
/* create a control dataset that holds all our datasets to be investigated */
data datasets;
input libname :$8. memname :$32.;
libname = upcase(libname);
memname = upcase(memname);
/* SAS keeps all libnames and memnames in uppercase */
datalines;
work class
;
/* a macro to calculate the min and max for all nuemric variables in a dataset */
%macro get_minmax(libname=,memname=);
data _null_;
set sashelp.vcolumn (
where=(libname = "&libname." and memname = "&memname." and type = 'num')
) end=eof;
if _n_ = 1
then call execute('
proc means data=' !! strip(libname) !! '.' !! strip(memname) !! ' min max;
var
');
call execute(' ' !! strip(name));
if eof
then call execute('
;
run;
');
run;
%mend;
/* a data step that executes the above macro for all datasets in our reference library */
data _null_;
set datasets;
call execute(cats('%nrstr(%get_minmax(libname=',libname,',memname=',memname,'))'));
run;
Result:
Die Prozedur MEANS Variable Minimum Maximum ---------------------------------------- Age 11.0000000 16.0000000 Height 51.3000000 72.0000000 Weight 50.5000000 150.0000000 ----------------------------------------
Yes. What does your data look like?
See this:
/* create an example table */
data class;
set sashelp.class;
run;
/* create a control dataset that holds all our datasets to be investigated */
data datasets;
input libname :$8. memname :$32.;
libname = upcase(libname);
memname = upcase(memname);
/* SAS keeps all libnames and memnames in uppercase */
datalines;
work class
;
/* a macro to calculate the min and max for all nuemric variables in a dataset */
%macro get_minmax(libname=,memname=);
data _null_;
set sashelp.vcolumn (
where=(libname = "&libname." and memname = "&memname." and type = 'num')
) end=eof;
if _n_ = 1
then call execute('
proc means data=' !! strip(libname) !! '.' !! strip(memname) !! ' min max;
var
');
call execute(' ' !! strip(name));
if eof
then call execute('
;
run;
');
run;
%mend;
/* a data step that executes the above macro for all datasets in our reference library */
data _null_;
set datasets;
call execute(cats('%nrstr(%get_minmax(libname=',libname,',memname=',memname,'))'));
run;
Result:
Die Prozedur MEANS Variable Minimum Maximum ---------------------------------------- Age 11.0000000 16.0000000 Height 51.3000000 72.0000000 Weight 50.5000000 150.0000000 ----------------------------------------
Quick question, what would I have to add to the code to get the table names to appear?
Maxim 2: Read the Log. Everything that's happened is in there.
If you need it in the output, add a title:
%macro get_minmax(libname=,memname=);
data _null_;
set sashelp.vcolumn (
where=(libname = "&libname." and memname = "&memname." and type = 'num')
) end=eof;
if _n_ = 1
then call execute('
title "Means for ' !! strip(libname) !! '.' !! strip(memname) !! '";
proc means data=' !! strip(libname) !! '.' !! strip(memname) !! ' min max;
var
');
call execute(' ' !! strip(name));
if eof
then call execute('
;
run;
');
run;
%mend;
If you have many table and many variables ,you can run the following code several times by setting FIRSTOBS= and OBS= options.
data _null_; set sashelp.vcolumn(keep=libname memname name type where=(libname='SASHELP' and type='num') obs=100) end=last; /*change sashelp as your library*/ if _n_=1 then call execute('proc sql; create table final_want as '); call execute(catt('select "',memname,'" as memname,','"',name,'" as name, min(',name,') as min,max(',name,') as max from sashelp.',memname)); /*change sashelp as your library*/ if not last then call execute('union'); else call execute(';quit;'); run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.