BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tdubbs
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
tdubbs
Fluorite | Level 6

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

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

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

tdubbs
Fluorite | Level 6

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                  

Ksharp
Super User

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

tdubbs
Fluorite | Level 6

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

Reeza
Super User

Please mark the question answered Smiley Happy

tdubbs
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1049 views
  • 4 likes
  • 4 in conversation