I attached test data and the code as follow:
%let Q = 3 ; /* quarter 3 */
%macro ytd_rollup(infile); proc sql ; create table ytd_temp as select loc_id, loc_desc, 100*(var0/volume) as percent format=5.1, volume, qtr from &infile. group by loc_id, loc_desc, qtr union select loc_id, loc_desc, 100*(sum(var0)/sum(volume)) as percent format 5.1, sum(volume) as volume , 999 as qtr from &infile. group by loc_id, loc_desc order by loc_id, loc_desc, qtr ; quit;
proc sql ; create table middle_data as select a.*, b.order from ytd_temp a left join order b on a.loc_desc = b.loc_desc ; quit;
proc sort data=middle_data; by order qtr ; run;
%IF &Q. EQ 2 %THEN %let var_list = Q2_per Q2_vol ; %ELSE %IF &Q. EQ 3 %THEN %let var_list = Q2_per Q2_vol Q3_per Q3_vol ; %ELSE %IF &Q. EQ 4 %THEN %let var_list = Q2_per Q2_vol Q3_per Q3_vol Q4_per Q4_vol ; %ELSE %let var_list = ;
Data final_data (keep= order loc_desc YTD_per YTD_vol Q1_per Q1_vol &var_list.) ; set middle_data; by order qtr; retain YTD_per YTD_vol Q1_per Q1_vol &var_list. ; if volume = 0 then volume = . ; if percent = 0 then percent = . ; select (qtr); when (171) do; Q1_per = percent; Q1_vol = volume; end; when (172) do; Q2_per = percent; Q2_vol = volume; end; when (173) do; Q3_per = percent; Q3_vol = volume; end; when (174) do; Q4_per = percent; Q4_vol = volume; end; when (999) do; YTD_per = percent; YTD_vol=volume; end; otherwise ; end; if last.order then output; run;
%mend ytd_rollup;
%ytd_rollup(initial_data)
... View more
