Here is the code: %macro ratio_m(id); proc sql; select id1, sum(x*z) as wx from table2 as P right join table1 as N on input(N.id2,8.)=P.id group by id1; create table temp2 as select orig_id, sum(y*z) as wy from table2 as P right join table1 as N on input(N.id2,8.)=P.id group by id1; %if %sysfunc(exist(final)) %then %do; drop table final; %end; create table final as select &i as indexRow, one.*, two.*, (wx/wy) as ratio from temp1 as one right join temp2 as two on one.id1=two.id1; quit; %mend; %macro main; %if %sysfunc(exist(Index_dsn))=1 %then %do; %if %sysfunc(exist(final_overall))=1 %then %do; proc sql noprint; select max(indexRow) into :indexRow from final_overall ;%let indexRow=%sysfunc(sum(&indexRow,1)); %put &indexRow; select max(indexRow) into :indexFinal from Index_dsn ;%let indexFinal=&indexFinal; %put &indexFinal; ;quit; %put Previous calculation ends on row &indexRow ; %end; %else %if %sysfunc(exist(final_overall))=0 %then %do; %put "First Iteration: Dataset final_overall does not exist"; proc sql noprint; select max(indexRow) into :indexFinal from Index_dsn; ; create table final_overall (indexRow num ,id1 char(10) ,ratio num); ;quit; %let indexFinal=&indexFinal; %let indexRow=1; %end; %end; %else %if %sysfunc(exist(Index_dsn))=0 %then %do; %put "Datasets Index_dsn and final_overall dont exist"; proc sql noprint; create table Index_dsn AS select distinct id1 from table1; ;quit; data Index_dsn; set Index_dsn; indexRow+1; run; proc sql noprint; select max(indexRow) into :indexFinal from Index_dsn; ; %let indexFinal=&indexFinal; %let indexRow=1; create table final_overall (indexRow num ,id1 char(10) ,ratio num); ;quit; %end; %do i=&indexRow %to &indexFinal; proc sql; select id1 into :id from Index_dsn where indexRow=&i ; %let id=&id; quit; %put &id; %ratio_m (&id); proc append base=.final_overall data=final force; run; %end; %mend; options nomlogic; %main
... View more