CODEWITHSUNDEEP
sas
Vaishak N Chandran
Vaishak N Chandran

Reputation: 77

finding max of many columns using proc sql statement

I am trying to write a PROC SQL query in SAS to determine maximum of many columns starting with a particular letter (say RF*). The existing proc means statement which i have goes like this.

proc means data = input_table nway noprint missing;
   var age x y z RF: ST: ;
   class a b c;
   output out = output_table (drop = _type_ _freq_) max=;
run;

Where the columns RF: refers to all columns starting with RF and likewise for ST. I was wondering if there is something similar in PROC SQL, which i can use?

Thanks!

Upvotes: 0

Views: 2701

Answers (1)

user667489
user667489

Reputation: 9569

Dynamic SQL is indeed the way to go with this, if you must use SQL. The good news is that you can do it all in one proc sql call using only one macro variable, e.g.:

proc sql noprint;
  select catx(' ','max(',name,') as',name) into :MAX_LIST separated by ','
  from dictionary.columns
  where libname = 'SASHELP' 
  and memname = 'CLASS'
  and type = 'num'
  /*eq: is not available in proc sql in my version of SAS, but we can use substr to match partial variable names*/ 
  and upcase(substr(name,1,1)) in ('A','W') /*Match all numeric vars that have names starting with A or W*/
  ;
  create table want as select SEX, &MAX_LIST
    from sashelp.class
    group by SEX;
quit;

Upvotes: 2

Related Questions