Proc Compare macro code for optimization

[umashankersaini]

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 :PF1- , :PT- , :BANK1- */

proc sql ;

   select   product_portfolio,product_type,BANK_CODE

      into :PF1 -:PF%left(&cntpf.),:PT1 -:PT%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

[umashankersaini]

Hi,

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

Regards

Uma Shanker Saini

SASCertGuru

[ballardw]

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.

[umashankersaini]

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