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
... View more