Hi. Thanks for some advice. It's funny you mention because that's what we've been trying to use - the use of cross variable and a call define for each value that needs different format For example: CODE: proc sql; create table dlystats as select 1 as sortit ,'TY' as period ,'Avg Items/Order' as metric ,sum(a.items) / sum(a.orders) as value format 10.2 from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 2 as sortit ,'TY' as period ,'Avg Order Amount' as metric ,sum(a.amount) / sum(a.orders) as value format dollar15.2 from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 3 as sortit ,'TY' as period ,'Total Items' as metric ,sum(a.items) as value format comma10. from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 4 as sortit ,'TY' as period ,'Total Orders' as metric ,sum(a.orders) as value format comma10. from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 5 as sortit ,'TY' as period ,'Total Sales' as metric ,sum(a.amount) as value format dollar15.2 from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 6 as sortit ,'TY' as period ,'Sales Plan' as metric ,b.pln_amt as value from sales_hour_sum a ,flashsum.ecomm_dly_pln b where b.date="&datecde"d union select 7 as sortit ,'TY' as period ,'Percent of Plan' as metric ,sum(a.amount) / b.pln_amt * 100 as value from sales_hour_sum a ,flashsum.ecomm_dly_pln b where b.date="&datecde"d union select 1 as sortit ,'LY' as period ,'Avg Items/Order' as metric ,sum(a.itemsly) / sum(a.ordersly) as value format 10.2 from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 2 as sortit ,'LY' as period ,'Avg Order Amount' as metric ,sum(a.amountly) / sum(a.ordersly) as value format dollar15.2 from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 3 as sortit ,'LY' as period ,'Total Items' as metric ,sum(a.itemsly) as value format comma10. from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 4 as sortit ,'LY' as period ,'Total Orders' as metric ,sum(a.ordersly) as value format comma10. from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 5 as sortit ,'LY' as period ,'Total Sales' as metric ,sum(a.amountly) as value format dollar15.2 from sales_hour_sum a ,flashsum.ecomm_dly_pln b union select 6 as sortit ,'LY' as period ,'Sales Plan' as metric ,b.pln_amt as value from sales_hour_sum a ,flashsum.ecomm_dly_pln b where b.date="&datecde"d - 364 union select 7 as sortit ,'LY' as period ,'Percent of Plan' as metric ,sum(a.amountly) / b.pln_amt * 100 as pct_plnly as value from sales_hour_sum a ,flashsum.ecomm_dly_pln b where b.date="&datecde"d - 364 ; options nocenter; proc sort data=dlystats; by sortit descending period; proc print data=dlystats; title 'dlystats'; proc report nowd data=dlystats; column metric period, value; title h=12pt "Ecommerce Demand Sales Summary: &datecde "; define metric / 'Metric' group order=internal style=Ýjust=right¨; define period / 'Period' across order=internal style=Ýjust=left¨; define value / 'Value' style=Ýjust=right¨; compute value; if metric='Avg Items/Order' then do; call define(_col_,'format','5.2'); call define(_col_, 'style', 'style=Ýjust=right cellwidth=10em¨'); end; if metric='Avg Order Amount' then do; call define(_col_,'format','dollar15.2'); call define(_col_, 'style', 'style=Ýjust=right cellwidth=10em¨'); end; if metric='Total Items' then do; call define(_col_,'format','comma10.'); call define(_col_, 'style', 'style=Ýjust=right cellwidth=10em¨'); end; if metric='Total Orders' then do; call define(_col_,'format','comma10.'); call define(_col_, 'style', 'style=Ýjust=right cellwidth=10em¨'); end; if metric='Total Sales' then do; call define(_col_,'format','dollar21.0'); call define(_col_, 'style', 'style=Ýjust=right cellwidth=10em¨'); end; if metric='Sales Plan' then do; call define(_col_,'format','dollar21.0'); call define(_col_, 'style', 'style=Ýjust=right cellwidth=10em¨'); end; if metric='Percent of Plan' then do; call define(_col_,'format','pctfmt.'); call define(_col_, 'style', 'style=Ýjust=right cellwidth=10em¨'); end; endcomp; run; OUTPUT but the output and formatting isn't quite where we need it to be. even some of the values like items & orders looks off since use of cross variable Obs sortit period metric value 1 1 TY Avg Items/Order 1.23 2 1 LY Avg Items/Order 1.23 3 2 TY Avg Order Amount 12.12 4 2 LY Avg Order Amount 123.12 5 3 TY Total Items 1234567.00 6 3 LY Total Items 1234567.00 7 4 TY Total Orders 1234567.00 8 4 LY Total Orders 1234567.00 9 5 TY Total Sales 123456789 10 5 LY Total Sales 123456789 11 6 TY Sales Plan 123456.12 12 6 LY Sales Plan 123456.12 13 7 TY Percent of Plan 12.12 14 7 LY Percent of Plan 12.12 Period LY TY Metric Value Value Avg Items/Order 1.12 1.12 Avg Order Amount $123.12 $12.12 Percent of Plan 12.1% 12.1% Sales Plan $123,123 $123,123 Total Items 1,123,123 1,123,123 Total Orders 1,123,123 1,123,123 Total Sales $123456789 $123456789
... View more