topic Re: Add categories with no values into summary report based on proc format categories in SAS Programming

Add categories with no values into summary report based on proc format categories

Ronein — Mon, 11 Oct 2021 10:33:13 GMT

Hello,

Lets say that I have created a summary table and the result is the summary table in the code below.

The task is to add categories that appear in proc format into the summary table.

What is the best and most useful way to do it?

Desired table is

A 10
B 30
C 0
D 0
E 20
F 0
G 80
H 0

Data Summarytbl;
Input Category $ Value;
Cards;
A 10
B 30
E 20
G 80
;
Run


proc format;
value $FFF 
'A'='Reason_A'
'B'='Type B'
'C'='Unknown reason'
'D'='Reason D'
'E'='Issue E'
'F'='Task F'
'G'='Task G'
'H'='Task H'
;
Run;

Re: Add categories with no values into summary report based on proc format categories

andreas_lds — Mon, 11 Oct 2021 10:41:50 GMT

Depends on how the summary table is created, proc summary e.g. has an option to create rows/columns for each value in a format. Please see the docs for details.

Re: Add categories with no values into summary report based on proc format categories

PeterClemmensen — Mon, 11 Oct 2021 10:42:34 GMT

Untested:

proc summary data = Summarytbl nway completetypes;
   class Category / preloadfmt;
   var Value;
   output out = want(drop = _:) sum=;
   format Category $FFF.;
run;

Re: Add categories with no values into summary report based on proc format categories

andreas_lds — Mon, 11 Oct 2021 10:49:54 GMT

Another idea, applicable if you can't change the process creating the summary dataset:

data Summarytbl;
   length Category $ 1 Value 8;
   input Category $ Value;
   cards;
A 10
B 30
E 20
G 80
;
run;


proc format;
   value $FFF 
      'A'='Reason_A'
      'B'='Type B'
      'C'='Unknown reason'
      'D'='Reason D'
      'E'='Issue E'
      'F'='Task F'
      'G'='Task G'
      'H'='Task H'
   ;
run;


proc format cntlout= FormatDef(keep= Start rename=(Start = Category));
   select $FFF;
run;


data want;
   merge FormatDef SummaryTbl;
   by Category;
   
   Value = coalesce(Value, 0);
run;

Re: Add categories with no values into summary report based on proc format categories

Ronein — Mon, 11 Oct 2021 10:52:14 GMT

I need to work on the summary table that was created.......and add to him the desired categories

Re: Add categories with no values into summary report based on proc format categories

andreas_lds — Tue, 12 Oct 2021 05:12:08 GMT

Then post the code you are using to create the summary table, if it is not proc summary.