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;
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
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.
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
Thanks Xia, that's really going to help keep my programs sleek.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.