Hello,
Fairly newbie to SAS. It's been about a week or so that I've been looking at stuff online and SAS code examples. Was wondering what would be best way to approach formatting the data to look like below. I'm able to get the data I need into a SAS dataset and create a temp table with some aggregates functions. How would you accomplish this? Is there a method to get the data formatted like below:
desired format
METRIC | TY | LY |
---|---|---|
When I do a proc print it looks something like this
proc report nowd data=dlystats;
column period metric value;
run;
Period metric value
LY Amount 266459509
LY AvgItems 4.66
LY AvgOrderAmt 107.55
LY Items 11534356.0
LY Orders 2477536.00
LY PctPlan 59.18
LY Plan 404151.00
TY Amount 146434520
TY AvgItems 3.07
TY AvgOrderAmt 88.34
TY Items 5094322.00
TY Orders 1657632.00
TY PctPlan 32.81
TY Plan 400587.69
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
Hi, I suggest that you investigate using PERIOD as an ACROSS item and METRIC as a GROUP item. Then you could use value nested underneath each unique value of PERIOD. You could also do this report with PROC TABULATE. The challenge will be whether you are going to want to apply different formats to different cells. Like having AMOUNT with $ but having ORDERS with just commas. That type of individualized formatting can only be done with PROC REPORT in a CALL DEFINE statement.
cynthia
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
After that SQL statements, Can't you use proc transpose , then you don't need proc report + across any more, just proc print ?
proc transpose data=dlystats out=want;
by sortit;
id period;
var value;
run;
Xia Keshan
thanks all. was able to resolve by using combo of transpose & across variable. onto the next enhancement now
Please mark the question answered
kinda stinks i can't mark 2 correct answers, since it was a combo deal that of suggestions that got me my solution. transpose with cross var
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.