Help using Base SAS procedures

formatting advice

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

formatting advice

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

METRICTYLY

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


Accepted Solutions
Solution
‎02-12-2015 05:10 PM
New Contributor
Posts: 4

Re: formatting advice

Posted in reply to Cynthia_sas

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 solution in original post


All Replies
SAS Super FREQ
Posts: 8,868

Re: formatting advice

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

Solution
‎02-12-2015 05:10 PM
New Contributor
Posts: 4

Re: formatting advice

Posted in reply to Cynthia_sas

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                  

Super User
Posts: 10,044

Re: formatting advice

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

New Contributor
Posts: 4

Re: formatting advice

thanks all.  was able to resolve by using combo of transpose & across variable.  onto the next enhancement now

Super User
Posts: 19,868

Re: formatting advice

Please mark the question answered Smiley Happy

New Contributor
Posts: 4

Re: formatting advice

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 296 views
  • 3 likes
  • 4 in conversation