topic Re: Loop through tables to get min and max value in SAS Programming

Loop through tables to get min and max value

RALL — Thu, 18 Jul 2019 04:02:14 GMT

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?

Re: Loop through tables to get min and max value

PeterClemmensen — Thu, 18 Jul 2019 05:28:14 GMT

Yes. What does your data look like?

Re: Loop through tables to get min and max value

Kurt_Bremser — Thu, 18 Jul 2019 05:41:32 GMT

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

Re: Loop through tables to get min and max value

Ksharp — Thu, 18 Jul 2019 13:07:22 GMT

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;

Re: Loop through tables to get min and max value

RALL — Fri, 19 Jul 2019 05:27:56 GMT

Thanks for this, it did the trick.

Re: Loop through tables to get min and max value

RALL — Mon, 22 Jul 2019 23:11:30 GMT

Quick question, what would I have to add to the code to get the table names to appear?

Re: Loop through tables to get min and max value

Kurt_Bremser — Tue, 23 Jul 2019 05:29:04 GMT

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;