formatting advice

tdubbs — Wed, 11 Feb 2015 21:00:30 GMT

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

Re: formatting advice

Cynthia_sas — Thu, 12 Feb 2015 00:12:33 GMT

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

Re: formatting advice

tdubbs — Thu, 12 Feb 2015 22:10:46 GMT

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',