BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Calcite | Level 5

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

%macro minmaxloop();
set work.result;
array Var _character_;
do over Var;
%mend minmaxloop;

this is my input (work.result):
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.



Accepted Solutions
Jade | Level 19
PROC SUMMARY can find min/max of character variables using IDGROUP statement.

View solution in original post

Diamond | Level 26

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;

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.

Paige Miller
Jade | Level 19
PROC SUMMARY can find min/max of character variables using IDGROUP statement.


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 3 in conversation