topic Proc Tabulate Query in ODS and Base Reporting

Proc Tabulate Query

scb — Mon, 14 Oct 2019 10:30:44 GMT

May I know how to get the total TYPE1 and total TYPE2 cases? The underlined total. Thanks.

Example Output

Type

Type1 Type2

Done WIP TOTAL Done WIP TOTAL

Period1 1 0 1 2 1 3

Period2 1 1 2 4 2 6

Total 2 1 3 6 3 9

DATA STATUS;
INPUT PERIOD $ STATUS $ TYPE $ CASE;
DATALINES;
201801 Done Type1 1
201801 Done Type2 1
201802 WIP Type1 1
201802 Done Type2 1
201802 Done Type1 1
201802 WIP Type2 1
201803 Done Type1 1
201803 WIP Type2 1
;
run;

proc tabulate data=status;
class Period Type Status ;
var case;
table
Period all='Total',
(Type*Status='')*(N=''*F=8.) / Misstext='0';
Run;

Re: Proc Tabulate Query

Patrick — Mon, 14 Oct 2019 12:21:19 GMT

Thank you for posting sample data in the form of a working SAS datastep and then also showing us the desired result based on this sample data. Helping us makes it so much easier to help you.

Now to become perfect you just would also need to post the code into a code window using the running man icon and then ideally also mark "the best/most helpful answer" as solution. I've "checked" on you and I have seen that many of your discussions are still "open" meaning you haven't marked one of the answers as solution.

Here what you could do:

proc tabulate data=status;
  class Period Type Status;
  var case;
  table
    Period all='Total',
    Type*(Status='' all='Total')*N=''*F=8. / Misstext='0';
Run;

And just in case you also need a total by Period:

proc tabulate data=status;
  class Period Type Status;
  var case;
  table
    Period all='Total',
    (Type*(Status='' all='Total') all='Total')*N=''*F=8. / Misstext='0';
Run;

Or this way - your pick

proc tabulate data=status;
  class Period Type Status;
  var case;
  table
    Period all='Total',
    ((Type all='Total')*Status='' all='Total')*N=''*F=8. / Misstext='0';
Run;

Re: Proc Tabulate Query

ballardw — Tue, 15 Oct 2019 15:52:07 GMT

I would recommend for additional flexibility that you use actual date values instead of pseudo values like 201801.

Using an appropriate format with an actual date value you can duplicate that YYYYMM appearance but you could by just changing the format for the data variable get things like annual, quarterly or custom interval summaries.