Here is a crude attempt to transpose my dataset. Looking for better alternatives. The "crap" field is a fix I read in a SUGI paper while dealing with both numeric and character variables while transposing. I actually dont need the crap field but I have to put it because I dont want the formats to get lost while transposing. proc sql; create table mthly_sumry as select country, product, month, /*sum( '$ SPENT'n) as sum_pln_spent, */ sum('Actual GRPs'n) as actual_grp format=comma20., sum('Actual IMPs'n) as actual_imp format=comma20., sum('Actual Spend'n) as actual_spend format=dollar20.2 , sum('Buy Now'n) as buynow, /*sum('Cl. Spots'n) as sum_clspot,*/ /*sum(Coupons),*/ /*sum(Date Aired),*/ sum('Planned GRPs'n) as planned_grp format=comma20., sum('Planned IMPs'n) as pln_imp format=comma20., sum('Planned Spend'n) as pln_spend format=dollar20.2, /*sum(Store),*/ /*sum(Test) ,*/ sum('VIS To BLU'n) as visits, (calculated actual_spend)/(calculated pln_spend) as spend_clearance format=percent10.2, (calculated actual_imp)/(calculated pln_imp) as imp_clearance format=percent10.2, (calculated actual_spend)/( calculated visits) as cpv /*sum(W25-54IMP),*/ /*sum(W25-54RTG)*/ from lasrlib.bb_may2015 group by country,product,month; quit; proc sql; select count(distinct month) as cntmth into :cnt_bb_dist_mth from lasrlib.bb_may2015; quit; %let cnt_bb_dist_mth_trm=%CMPRES(&cnt_bb_dist_mth); %put "the number of distinct weeks in compressed format: &cnt_bb_dist_mth."; proc sql; select distinct month as month into :bb_mths1-:bb_mths&cnt_bb_dist_mth_trm. from lasrlib.bb_may2015 order by month ; quit; run; %put "&bb_mths1-&&bb_mths&cnt_bb_dist_mth_trm."; Data basedataset; run; %macro _loopthrumths; %do i = 1 %to &cnt_bb_dist_mth_trm; %put &&bb_mths&i.; proc sql; create table month&&bb_mths&i. as select * from mthly_sumry where upcase(month)=upcase("&&bb_mths&i.") ; quit; data tmp_month&&bb_mths&i.; set month&&bb_mths&i.; month_filter=month; crap="crap"; run; proc sort data= tmp_month&&bb_mths&i.; by country product month_filter; run; proc transpose data=tmp_month&&bb_mths&i. out=mthly_smry_&i._tr (rename=(_name_=metric) ) ; by country product month_filter; id month; var actual_grp actual_imp actual_spend buynow planned_grp pln_imp pln_spend visits crap ; run; data basedataset (drop=crap); set basedataset mthly_smry_&i._tr ; if metric in ('crap'," ",'') then delete; Metric=upcase(TRANWRD(metric,"_"," ")); run; proc sql; create view monthly_smry_final as select * from basedataset ; quit; %end; %mend _loopthrumths; %_loopthrumths /* Drop existing table */ %vdb_dt(LASRLIB.LSR_BBMAY2015_MTHSMRY_TR); data LASRLIB.LSR_BBMAY2015_MTHSMRY_TR ( ); set monthly_smry_final ( ); run;
... View more