topic Re: summary stats for many vars in SAS Programming

summary stats for many vars

Ronein — Sun, 08 Jun 2025 10:46:05 GMT

Hello

Lets say that I want to run statistics on multiple variables.

Lets say that this code is working 100% and I get desired results.

My question-

Is there better way to write the code (using proc step instead of proc sql)?

Please note that in real word I run it on 300 fields and data set has 2 million rows


%macro dist(VAR,tbl,level,format);
%do j=1 %to 6;
%let version=%scan(&Vector_Versions.,&j.,+);

proc sql;
create table _dist_ as
select Left("&VAR.") as Var_name  LENGTH=300,
        put(&VAR.,&Format.) as Cat LENGTH=100,
		        count(*) as nr,
				calculated nr/(select count(*) as total_nr from  &tbl.) as PCT format=percent8.5
from &tbl.
group by  calculated CAT
union all
select  Left("&VAR.") as Var_name  LENGTH=300,
         'Total'  as CAT,
		 count(*) as nr,
		 1 as PCT format=percent8.5
from  &tbl.
;
quit;

proc sort data=_dist_;
by var_name level cat_order;
Run;

proc append data=_dist_ base=r_r.Accum_tbl force;quit;
%end;
%mend dist;
%dist(Var=Wealth,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.)
%dist(Var=LoansBalance ,tbl=Y_ttt&version.,level=Y,Format=Fmt_continuous.)
%dist(Var=AVG_PAYOFF_LAST_MON,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.)
%dist(Var=HIYUV_EXLISCAR_AMT,tbl=L_ttt&version.,level=L,Format=Fmt_1F.)


%macro dist(VAR,tbl,level,format);
%do j=1 %to 6;
%let version=%scan(&Vector_Versions.,&j.,+);

proc sql;
create table _dist_ as
select Left("&VAR.") as Var_name  LENGTH=300,
        put(&VAR.,&Format.) as Cat LENGTH=100,
		        count(*) as nr,
				calculated nr/(select count(*) as total_nr from  &tbl.) as PCT format=percent8.5
from &tbl.
group by  calculated CAT
union all
select  Left("&VAR.") as Var_name  LENGTH=300,
         'Total'  as CAT,
		 count(*) as nr,
		 1 as PCT format=percent8.5
from  &tbl.
;
quit;

proc sort data=_dist_;
by var_name level cat_order;
Run;

proc append data=_dist_ base=r_r.Accum_tbl force;quit;
%end;
%mend dist;
%dist(Var=Wealth,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.)
%dist(Var=LoansBalance ,tbl=Y_ttt&version.,level=Y,Format=Fmt_continuous.)
%dist(Var=AVG_PAYOFF_LAST_MON,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.)
%dist(Var=HIYUV_EXLISCAR_AMT,tbl=L_ttt&version.,level=L,Format=Fmt_1F.)

Re: summary stats for many vars

Tom — Sun, 08 Jun 2025 13:33:04 GMT

Here are two different ways using PROC SUMMARY or PROC FREQ to gather all of the counts you need in one pass through the data.

Let's use SASHELP.CLASS as our example dataset and get the counts for AGE and SEX variables.

%let varlist=age sex;

proc summary data=sashelp.class chartype ;
  class &varlist. / missing;
  types () &varlist. ;
  output out=summary;
run;

proc print data=summary;
run;

ods select none;
ods output onewayfreqs=freq;
proc freq data=sashelp.class ;
  tables &varlist. / missing ;
run;
ods select all;

proc print data=freq;
run;

You could then add a data step to collapse the multiple columns into one.

For example for the PROC SUMMARY output you could do something like this:

data _dist_;
  attrib Var_name  length=$300;
  attrib Cat length=$100;
	attrib nr length=8;
  attrib PCT length=8 format=percent8.5 ;
  set summary ;
  var_name=scan("Total &varlist",1+indexc(_type_,'1'),' ');
  Cat = vvaluex(var_name);
  nr = _freq_;
  retain Total;
  if var_name='Total' then total=_freq_;
  pct = nr / total;
  keep Var_name -- PCT;
run;

proc print data=_dist_;
run;

And for the PROC FREQ output something like this:

data _dist_;
  attrib Var_name  length=$300;
  attrib Cat length=$100;
	attrib nr length=8;
  attrib PCT length=8 format=percent8.5 ;
  set freq end=eof;
  var_name=substr(Table,7);
  Cat = vvaluex(cat('F_',var_name));
  nr = frequency;
  pct = percent/100;
  output;
  if eof then do;
    var_name='Total';
    cat=' ';
    nr=cumfrequency;
    pct=cumpercent/100;
    output;
  end;
  keep Var_name -- PCT;
run;

proc print data=_dist_;
run;

PS: Notice that PERCENT8.3 is too short to consistently display 3 decimal places. You forgot to leave space for ( ) that PERCENT format adds around negative values.

Re: summary stats for many vars

Ronein — Sun, 08 Jun 2025 16:48:07 GMT

The question is- about running time?
Which way do you think is lowest running time? Should have significant difference in run time?is proc sql have the history running time??

Re: summary stats for many vars

Tom — Sun, 08 Jun 2025 17:10:11 GMT

@Ronein wrote:
The question is- about running time?
Which way do you think is lowest running time? Should have significant difference in run time?is proc sql have the history running time??

Two points to consider.

The PROCs I mentions should be able process ALL of the variables of interest in one pass thru the dataset. Your method re-reads the dataset once for each variable.

SAS procs have been optimized for performance. It might be very hard for you to write code that is as efficient.

Re: summary stats for many vars

Kurt_Bremser — Sun, 08 Jun 2025 18:18:29 GMT

@Ronein wrote:
The question is- about running time?
Which way do you think is lowest running time? Should have significant difference in run time?is proc sql have the history running time??

Once again, Maxim 4: Try It.

But @Tom 's single step approach must be faster than your 300 SQL steps.

Re: summary stats for many vars

Ronein — Sun, 08 Jun 2025 18:48:40 GMT

Please note thst in grouping I used different user defined formats .Did Tom used it?

Re: summary stats for many vars

Kurt_Bremser — Sun, 08 Jun 2025 19:21:09 GMT

So your question is: will the procedures use the formatted values for grouping?

Create a small example dataset and test this.

Re: summary stats for many vars

Ronein — Sun, 08 Jun 2025 19:35:44 GMT

My question if better to group in data step and then used the grouped vars in summary proc?

or use the user defined formats on 300 variables in summary proc?

Re: summary stats for many vars

Ronein — Sun, 08 Jun 2025 19:43:53 GMT

I dont understand , In my code I used

format=percent8.5

What do you recommend me to change here?

Do you recommend me use percent8.5 or percentn8.5?

data test;
length format $50.;
length var $50.;
format='percentn8.5'; var=put(0.10412456435, percentn8.5); output;
format='percent8.5'; var=put(0.10412456435, percent8.5); output;
format='percentn8.5'; var=put(-0.10412456435, percentn8.5); output;
format='percent8.5'; var=put(-0.10412456435, percent8.5); output;
RUN;

Re: summary stats for many vars

Tom — Mon, 09 Jun 2025 02:26:19 GMT

@Ronein wrote:

I dont understand , In my code I used
format=percent8.5
What do you recommend me to change here?

Do you recommend me use percent8.5 or percentn8.5?
data test;
length format $50.;
length var $50.;
format='percentn8.5'; var=put(0.10412456435, percentn8.5); output;
format='percent8.5'; var=put(0.10412456435, percent8.5); output;
format='percentn8.5'; var=put(-0.10412456435, percentn8.5); output;
format='percent8.5'; var=put(-0.10412456435, percent8.5); output;
RUN;

The WIDTH value of the format specification is the total number of character. The DECIMAL value is the number of characters after the decimal place. The PERCENT format needs 2 character for the optional parentheses, one for the required % and one for the optional (depending on if decimal places are requested) decimal point. The percentages in your example program could range between 100% and 0%. So you need three digits to the left of the decimal place. That means you need a width of 12 to be able to have room for 5 decimal places. 4 + 5 + 3 = 12.

534  data _null_;
535    do p=1,.1,.01,.001,.0001,.00001;
536       put p best12. +1 p percent8.5 +1 p percent12.5;
537    end;
538  run;

           1  100.0%   100.00000%
         0.1  10.00%    10.00000%
        0.01  1.000%     1.00000%
       0.001  .1000%     0.10000%
      0.0001  .0100%     0.01000%
     0.00001  .0010%     0.00100%

Re: summary stats for many vars

Tom — Mon, 09 Jun 2025 02:35:54 GMT

@Ronein wrote:
Please note thst in grouping I used different user defined formats .Did Tom used it?

If you need to attach formats to the variables that are different than the ones that are permanently attached to them in the dataset then you can add a FORMAT statement to the PROC step.

proc format ;
value age low-12 = '12 and under'
          13-high = '13 and over'
;
value $sex 'M'='Male' 'F'='Female';
run;

Re: summary stats for many vars

Ronein — Mon, 09 Jun 2025 06:00:23 GMT

so i need format percent12.5?

Re: summary stats for many vars

data_null__ — Tue, 10 Jun 2025 14:20:52 GMT

If you add the MLF option to the CLASS statement SAS will convert numeric variables (AGE) to character variables. This makes it easier to work with &VARLIST in the OUTPUT data. Can even make ARRAY _V &VARLIST;

Also, DESCENDTYPES will output data in VARLIST order.

proc summary data=sashelp.class chartype DESCENDTYPES;
  class &varlist. / missing MLF;
  types () &varlist. ;
  output out=summary;
run;

Re: summary stats for many vars

Kathryn_SAS — Tue, 10 Jun 2025 13:58:55 GMT

One small correction: the option is MLF (short for multilabel format).

Re: summary stats for many vars

ballardw — Wed, 11 Jun 2025 20:53:05 GMT

@Ronein wrote:
The question is- about running time?
Which way do you think is lowest running time? Should have significant difference in run time?is proc sql have the history running time??

In general Proc Freq and Summary are both more efficient than Proc SQL. In many cases faster by order(s) of magnitude. Reloading the data set multiple times alone for each variable would take many times the execution of the other procedures. Since you want percentages and no other procedures than counts then Proc Freq is almost certainly the fastest solution UNLESS you are using multiple grouping variables in multiple combinations i.e. a count by region, polity, sex, ; region and sex; polity and sex. Doing multiples of these might make Proc Summary preferred as the default counting tool.

Consider:

proc summary data=sashelp.cars;
   class  make  type origin drivetrain enginesize cylinders;
   output out=work.carsummary ;
run;

If you look at the _type_ = 1 then _freq_ is total number of observations in the data set with the given number of cylinders in the data. _type_=3 then _freq_ is the number of cylinders for the given Engine size. _type_=5 is the number cylinders given the drive train. _type_=11 is the number of cylinders of the engine for engine size for given value of Origin. Many combinations represented and can be selected by _type_. And custom format for the _TYPE_ variable could make a very useful report tool. The order of variables on the CLASS statement determines the values of the combinations so if the Class statement is changed any dependencies of _type_ have to be considered for selection or reporting.

Different formats applied to different variables will not have an issue with Proc Freq or Summary. If you are using different grouping formats for the same variable that would likely be an issue. Since you don't show any of the custom formats you use in the code we really can't tell what the effects might be.

Re: summary stats for many vars

RichardAD — Thu, 12 Jun 2025 12:27:12 GMT

If you want the summary output as a data set, you will also want to look into the Proc MEANS option STACKODSOUTPUT.

You are summarizing data in different data sets (tbl=Y_TTT*|L_TTT*) you will need, at a minimum, one use of Proc MEANS per table.

If the variables being summarized in the separate table are exclusive to a table, you probably want to stick to one MEANS per table. However, when producing output data for a wide audience, you would be well served to have the source data set name in the summary results.

If the data set structures are similar enough in 'identity of measure (i.e. customer id and region)' and the measure are in different data sets, perhaps due to policy of splitting data by year or month, you can stack the data sets and use one Proc MEANS on the stack. If you are stacking data sets it is good practice to incorporate the source data set name in the summary results if you need that temporality as a categorical variable. The stacking can be a delivered through a view.

With the proper hardware 2m x 100 summarization will happen quickly.