Proc Report - Print all format values even where no data?

[RB1Kenobi]

Hello

I'm running SAS 9.3

In the example below, no data exists where product="B".  But I'd like the proc report output to display "BRAVO, 0".

I know I can undertake additional data-steps to force the existence of the required observations, but I wondered if there was a solution within PROC REPORT itself?

proc format;

  value $ prodfull

  'A'='Alpha'

  'B'='Bravo'

  'C'='Charlie'

  ;

run;

data demodata;

  do i=1 to 33;

  do t='A','C';

  sales=i; product=t; output;

  sales=22;product="C"; output;

  end;

  end;

run;

Proc report data=work.demodata;

title 'How can I display zero sales for Product Bravo?';

column Product sales;

define product / group f=$prodfull. 'Product' ;

define sales / sum 'Sales';

run;title;

[RW9]

Not as far as I know.  You could of course do a pro means with / mlf option to get the results.  Me personally I always generate the table as I want to see it in the output first, then do a proc report.  The reason is twofold, first it keeps the proc report minimal, and you have more options in datastep, secondly I need to keep the dataset so that someone else can QC it.

[Ksharp · Accepted solution]

use completerows + preloadfmt

proc format;
  value $ prodfull
  'A'='Alpha'
  'B'='Bravo'
  'C'='Charlie'
  ;
run;
 
 
data demodata;
  do i=1 to 33;
  do t='A','C';
  sales=i; product=t; output;
  sales=22;product="C"; output;
  end;
  end;
run;
 
options missing='0'; 
Proc report data=work.demodata completerows  nowd;
title 'How can I display zero sales for Product Bravo?';
column Product sales;
define product / group f=$prodfull. preloadfmt 'Product' ;
define sales / sum 'Sales';
run;title;

Xia Keshan

Message was edited by: xia keshan

[RB1Kenobi]

Thanks Xia, that's really going to help keep my programs sleek.