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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.