DATA Step, Macro, Functions and more

Proc Compare macro code for optimization

Reply
Frequent Contributor
Posts: 116

Proc Compare macro code for optimization

Hi All,

I am looking for your kind help to optimize following macro code which provides the proc compare report of same data set from two different environment. I am running this code on a huge data set and this simple code is taking around 2 hours. It would be great, if we can minimize the time . Please help. Suggestion would be heartily appreciated.

%let Basepath=%str(E:\SAS Practice folder\base);

%let Compare=%str(E:\SAS Practice folder);

libname base "&Basepath.";

libname compare "&Comparepath.";

DATA Base.TARGET;

input product_portfolio $ product_type $ BANK_CODE $ Account_id $ value;

datalines;

CC CL ABC 1 10

GC HL GGB 2 20

CC ML BBC 3 30

;

RUN;

DATA Compare.TARGET;

input product_portfolio $ product_type $ BANK_CODE $ Account_id $ value;

datalines;

CC CL ABC 1 10

GC HL GGB 2 20

CC ML BBC 3 33

;

RUN;

%macro report(BASETABLE,COMPARETABLE,pf,pt,var,bankcode);

%let repopath="C:\Users\Uma\Desktop\report\SIT_PROD_&&Table_Name._&&pf.._&&pt.._&&bankcode...txt";

ods _all_ close;

ods listing file=&repopath;

proc compare base = &&BASETABLE comp = &&COMPARETABLE

  method=absolute

     maxprint = (20,1000);

  id &var;

where

strip(upcase(PRODUCT_PORTFOLIO)) = "&&pf."

and strip(upcase(product_type))="&&pt."

and strip(upcase(bank_code))="&&bankcode.";

run;

ods listing close;

%mend;

/*Below Macro definition serves the purpose of creating macro variable of combination of distinct values of bank code,

Portfolio code and Product Type code and saves it in output text file.

*/

%macro maincall(Table_Name,ID);

PROC SORT DATA=base.&&Table_Name nodupkey OUT=TARGET ;

BY BANK_CODE PRODUCT_PORTFOLIO product_type;

RUN;

proc sql;

select count( product_portfolio) into :cntpf

from TARGET

;

quit;

proc sql;

select count( product_type) into :cntpt

from TARGET

;

quit;

proc sql;

select count( BANK_CODE) into :cntbc

from TARGET

;

quit;

ods _all_ close;

ods listing file="C:\Users\Uma\Desktop\report\Parameters_for_&&Table_Name..txt";

/*Depending on previous counts, creating macro variables Smiley TongueF1- , Smiley TongueT- , :BANK1- */

proc sql ;

   select   product_portfolio,product_type,BANK_CODE

      into Smiley TongueF1 -Smiley TongueF%left(&cntpf.),Smiley TongueT1 -Smiley TongueT%left(&cntbc.),:Bank1 -:Bank%left(&cntbc.)

      from target;

QUIT;

ods listing close;

proc sort data =base.&&Table_Name out=BASETABLE;

by &id;

proc sort data =compare.&&Table_Name out=COMPARETABLE;

by &id;

/*parameterised macro call to generate compare reports*/

%do i = 1 %to &cntpf;

%report(BASETABLE,COMPARETABLE,&PF%left(&i.),&PT%left(&i.),&ID,&Bank%left(&i.));

%END;

%mend;

*Very first macro call, this is start of execution of program*/

%maincall(Target, ACCOUNT_ID);

Regards

Uma Shanker Saini

SASCertGuru

Frequent Contributor
Posts: 116

Re: Proc Compare macro code for optimization

Hi,

Mentioned code is ready to use, Just looking for some optimization.

Regards

Uma Shanker Saini

SASCertGuru

Super User
Posts: 10,550

Re: Proc Compare macro code for optimization

One place is to replace:

proc sql;

select count( product_portfolio) into :cntpf

from TARGET

;

quit;

proc sql;

select count( product_type) into :cntpt

from TARGET

;

quit;

proc sql;

select count( BANK_CODE) into :cntbc

from TARGET

;

quit;

With:

proc sql;

select count( product_portfolio),  count( product_type), count( BANK_CODE) into :cntpf , :cntpt ,  :cntbc

from TARGET

;

quit;

and read the file once instead of 3 times.

Frequent Contributor
Posts: 116

Re: Proc Compare macro code for optimization

Hi Ballardw,

Thanks for your quick response and help. Your suggestion is working fine. I have tested this one with small data set and i got the difference of 2 mins so i am so sure that it would help a lot on huge data as well.

If something is possible to reduce the sorting time of data set then it would help a lot...

looking for more inputs from your side and from group as well.....

Thank you so much...

Regards

Uma Shanker Saini

SAS Default Sample Data Sets Library With Proc Datasets procedure

Ask a Question
Discussion stats
  • 3 replies
  • 295 views
  • 3 likes
  • 2 in conversation