proc summary,tabulate,transpose or means

[Q1983]

Proc sql;

Create table main3 as

Select ln,scenario,wk_station,

Case when ln ne ‘’ then 1 else ‘’ end as cnt_ln;

Quit;

Sample data

Ln         scenario           wk_station      cnt_ln

11         legal                             a                  1

12       legal                             a                   1

13       bk                                                      1

14      loss_mit                     c                   1

Desired output

scenario                           a          c      (blank) if wk_station is blank          Grand_Total

Legal                                2                                                                                 2

bk                                                                             1                                          1

loss_mit                                      c                                                                       1

Grand_Total                  2           1                            1                                         4

Essentially I need to summary these and show totals both horizontal and vertical.  it appears I may need a combination of proc statements, any ideas.  Note if the wk_station is blank I want to assign a 1

[ballardw · Accepted solution]

If the data is only counts then this may provide a starting point:

proc tabulate data=have;

     class scenario;

     class wk_station / missing ;

     freq cntl_ln; /* this says to use the value of the variable as a count*/

     table scenario='' All="Grand Total",

              (wk_station='' All="Grand Total")*n='

               / misstext=' ' ';

run;

the ='' suppress the label or variable name appearing in the output