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;
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: