CODEWITHSUNDEEP
sasproc-sql
Eli
Eli

Reputation: 65

Using Proc sql to get statiscs for many variables

I want to use Porc sql to create a data set that contains some statistics as min and max for a lots of variables. The code below only returns a data set with min and max for first variable, for the rest of variables min and max are not show in the data set.

  proc sql;
  CREATE TABLE Lib.VarNum AS 
  %do i=1 %to &nvars;
  select  min(%SCAN(&numvar,&i)) as Min%SCAN(&numvar,&i),
     max(%SCAN(&numvar,&i)) as Max%SCAN(&numvar,&i)
  from &data (keep= _numeric_);
  %end;
  quit;

Somebody can help me?

Upvotes: 0

Views: 55

Answers (1)

Kiran
Kiran

Reputation: 3315

using proc means is best way to do this. 

  proc means data=sashelp.cars noprint;
 var _numeric_;
 output out=want (drop= _type_ _freq_ )min(_numeric_) =
 max(_numeric_) =/autoname;
 run;

but if you want to so it by Proc SQL easiest to macrovariables from dictionary.columns and use them in your tables.

 /* creating macrovariables using dictionary.columns*/
 proc sql noprint;
select 'min('|| trim(name)||') as min_'||name,
   'max('|| trim(name)||') as max_'||name 
   into :min separated by ',' , :max separated by ','
from dictionary.columns
where libname ='SASHELP'
and memname ='CARS'
and upcase(type) ='NUM';

Values of macrovariable can be checkedly and only partially shown

 %put &min;
  min(MSRP) as min_MSRP,min(Invoice) as min_Invoice,min(EngineSize) as min_EngineSize

use this macro variables in proc sql statement as shown below.

proc sql;
create table want as 
select &min, &max from sashelp.cars;

Upvotes: 1

Related Questions