topic looping through each column with macro in New SAS User

looping through each column with macro

sirapolw — Tue, 09 Jul 2019 10:35:56 GMT

%macro minmaxchar(Var);
proc sql;
select min(&Var) as Minimum,max(&Var) as Maximum from
work.result;
quit;
%mend minmaxchar;

%macro minmaxloop();
data work.sc;
set work.result;
array Var _character_;
do over Var;
%minmaxchar(Var);
end;
run;
%mend minmaxloop;

this is my input (work.result):
A B C D
--------
1 dv 4 t
3 aa 5 zz
2 cs 6 ac

this is my wanted output
Max Min
-------
3 1
dv aa
6 4
zz ac

I am looking for a way to get the minimum and the maximum of each column (I want to make it work for both char and numeric columns), but I need to use the sql min() and max() instead of sas because sql min() max() can receive char as a parameter and compute the min and max as in ASCII order. The only problem with sql min()/max() is that it can only receive one argument at a time, so I would need to enter the column name in order for it to work. However, I want the code to be as dynamic as possible so I want a way to put in a macro variable as a parameter in the min/max function and have the macro variable be the iterator through all the columns. My code above does not work because the proc sql cannot be used within the looping data step. Please enlightened me experts.

Re: looping through each column with macro

PaigeMiller — Tue, 09 Jul 2019 12:06:36 GMT

First, macros are not necessary, as SAS has many built-in ways of finding minimums and maximums of a column.

Next, even if you were to run your macro, it would never work because you cannot run PROC SQL inside a DATA step as you are trying to do. Further, in order to get any macro to work, the advice (which you should be following) is to get the code to work on one variable without using macros, before you try to turn it into a macro.

For your numeric variables, one way to get the min and max of the columns simply is via PROC SUMMARY

proc summary data=have;
    var _numeric_;
    output out=want min= max=/autoname;
run;

Since there is no such thing as a minimum of character variables, you'll have to define what you mean. If you just want the character variable levels counted, you can use PROC FREQ.

Re: looping through each column with macro

data_null__ — Tue, 09 Jul 2019 12:52:32 GMT

PROC SUMMARY can find min/max of character variables using IDGROUP statement.