How to optimize PROC SUMMARY

[alepage]

Hello, I am doing a sample proc summary and on SAS EG it haven't completed yet.  

I have run the same code on the unix server and I have my output dataset.

Is there options I can add to my sas code in order to see which resources are use and why it takes so much time.

Is there some setting I can use to optimize the execution.

%let FName=PNC_PROD_ALL_FORMATTED_202301;

few steps... 

proc sort data=&FName.;
by Policy_number INCEPTION_DATE_ADJ; 
run;

libname dest1 base '**********/outputds';

proc summary data= &FName. print  ;
class broker_number year month type3 ;
var  nbu_pol nbu_prem;
output out= new_business_summary (where=(_type_ =15) drop= _freq_) sum=;
run;

Okay, from the beginning until the proc sort, it takes one or two minutes.  But when comes the time for the proc summary, it is like sas eg is running and running.

Any solution for this issue

[Quentin]

Hi,

SAS EG doesn't run code, it sends code to a SAS session (usually a remote server).

You said the code runs faster when you submit it on the unix server.

Is your EG session connected to the same unix server, or a different server?  

If these simple steps are running noticeably slower, you might want to work with your admin to investigate the difference.  It looks like you're using WORK datasets, so potentially it could be WORK is allocated to slow disk or distant disk.  If the EG job literally never completes, then there could be network problems.  If it does complete, can you share the log, I'm curious what it shows for execution times for each step.

[SASKiwi]

It will run a lot faster if you use the NWAY option instead of using a WHERE _type_ = 15:

proc summary data= &FName. print  nway;
class broker_number year month type3 ;
var  nbu_pol nbu_prem;
output out= new_business_summary  (drop= _freq_) sum=;
run;

Using the PRINT option will also slow it down particularly if you are printing a large output.

[Kurt_Bremser]

With a high cardinality of your CLASS variables, the output will be very large, and processing this always takes a lot of time (and memory) in EG. Consider storing the result in a dataset instead of printed output.

[alepage]

Hello Kurt,

I am not very familiar with the proc summary procedure. How do we store the results in a dataset instead of a printed output.  Also, there is the html file that is created which is pretty large.

[Cynthia_sas]

Hi: You're already using the OUTPUT statement with the OUT= option, so you're creating an output dataset. It is the PRINT option that is producing the output results in HTML format. You might consider removing that option and see whether it runs faster.
Cynthia

[Kathryn_SAS · Accepted solution]

You are already creating an output data set with the OUTPUT statement. I would suggest removing the PRINT option and adding NWAY as shown below. Since you are also sorting the data set, you could add one of the CLASS variables to the PROC SORT and then move that to a BY statement in PROC SUMMARY. I have added an ODS statement to suppress any automatic output. The FULLSTIMER option will add additional processing information to the log.

options fullstimer;
proc sort data=&FName.;
by Policy_number INCEPTION_DATE_ADJ broker_number; 
run;

libname dest1 base '**********/outputds';

ods _all_ close;
proc summary data= &FName. nway  ;
by broker_number;
class year month type3 ;
var nbu_pol nbu_prem;
output out= new_business_summary(/*where=(_type_ =15)*/ drop= _freq_) sum=;
run;

ods listing;
proc print data=new_business_summary;
run;