SAS Calculate percentiles and save to macro variable

Question

I have the following code to calculate percentiles.

proc univariate data=sashelp.cars;
  var Horsepower 
  output pctlpre=P_ pctlpts= 50, 75 to 100 by 5;
run;

I would like to assign these percentiles to a macro variable (so 1 macro variable per percentile) on the fly. Is there any smart way to do this?

Answer 1

What does "smart way to do this" mean? What will you do with the macros as you call them?

proc univariate data=sashelp.cars noprint;
   var Horsepower;
   output pctlpre=P_ pctlpts= 50, 75 to 100 by 5;
   run;
proc print;
   run;
data _null_;
   set;
   array P_ P_:;
   do over p_;
      call symputx(vname(p_),p_);
      end;
   stop;
   run;
%put _user_;

Based on input from OP about what they are really doing there is no need to put data into macro variables. My opinion it is never "smart" to put data into macro variables when those variable will be used in calculations.

This creates new variables CAP_: capped at &p.

%let p=90;
ods select none;
ods output summary=ptile;
proc means data=sashelp.cars stackods p&P.;
   run;
ods output close;
ods select all;
proc print;
   run;
/*Flip the rows into 1 obs.  Vars with prefix P&P._*/
proc transpose data=ptile out=ptile2(drop=_:) prefix=P&P._;
   var P&P.;
   id variable;
   run;
/*Flip the rows to create new CAP_ variables.  Just for the names*/
proc transpose data=ptile out=cap(drop=_:) prefix=Cap_;
   var P&P.;
   id variable;
   run;
/*create capped variables*/
data capped;
   set sashelp.cars;
   array _v _numeric_;
   /*Create array of new variables CAP_*/
   if 0 then set cap; 
   array _cap cap_:;
   call missing(of _cap[*]);
   /*Create array of Ptile variables*/
   if _n_ eq 1 then set ptile2;
   array _tile P&P._:; 
   *drop P&P._:;
   do over _v;
      _cap = min(_tile,_v);
      end;
   run;
proc print;
   run;

Answer 2

May be not so smart, but will work, i think.

proc univariate data=sashelp.cars;
  var Horsepower;
  output out=table pctlpre=P_ pctlpts= 50, 75 to 100 by 5;
run;

proc sql noprint;
   select 'call symput('|| "'" ||strip(name)||"'," || strip(name) || ");"  
   into:name_list separated by ' ' 
   from dictionary.columns 
   where libname ="WORK" and memname="TABLE";
quit;

data _null_;
   set table;
   &name_list;
run;

%put _ALL_;

Proc sql generates code on fly: call symput('P_50',P_50); call symput('P_75',P_75); call symput('P_80',P_80); call symput('P_85',P_85); call symput('P_90',P_90); call symput('P_95',P_95); call symput('P_100',P_100);

OUTPUT:

GLOBAL P_100 500

GLOBAL P_50 210

GLOBAL P_75 255

GLOBAL P_80 275

GLOBAL P_85 295

GLOBAL P_90 302

GLOBAL P_95 340

Answer 3

Here's a way to do this using only a single proc sql:

proc univariate noprint data=sashelp.class;
  var height; 
  output out=percentiles pctlpre=P_ pctlpts= 50, 75 to 100 by 5;
run;

proc sql noprint;
  select 
    name, 
    cats(':',name)
  into 
    :COL_NAMES separated by ',', 
    :MVAR_NAMES separated by ','
  from sashelp.vcolumn 
  where 
    libname = "WORK" 
    and memname = "PERCENTILES"
  ;
  select &COL_NAMES into &MVAR_NAMES
  from percentiles;
quit;

Result: 1 macro variable per percentile, with the same names as the univariate output.