My Requirement :
I want to display hyphen (-) in my excel report when the values are zero(0).
My Code:
PROC FORMAT;
value zero 0='-';
RUN;
PROC REPORT data=sasuser.test nofs headline headskip missing split='/'
style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9
fontfamily=helvetica fontsize=3 textalign=r font_weight=bold] ;
WHERE desc_code='3';
column ID Count_Of_Scripts Count_Of_Members;
define ID / ORDER=data 'ID Code' format=$11. order=formatted;
define Count_Of_Scripts / analysis SUM CENTER 'Count Of Scripts' missing;
define Count_Of_Members / analysis SUM CENTER format=zero. 'Count Of Members' missing;
rbreak after / summarize;
COMPUTE AFTER;
BRND_NM='Total';
ENDCOMP;
Problem:
It works fine when the data is actually 0 in column Count_Of_Members. But, It displays * in some places (not all values ) even when Count_Of_Members has valid numerical value
any sugesstions on how to better deal about displaying a hyphen (-) when the column values are zero.
Thanks,
M
hi
You should add a default length like so
proc format fmtlib;
value zero (default=12)
0='-'
;
run;
As the format iscurrently defined, the default length is 1, this is why you get the "*"
The FMTLIB option will print information on the format created.
Bruno
hi
You should add a default length like so
proc format fmtlib;
value zero (default=12)
0='-'
;
run;
As the format iscurrently defined, the default length is 1, this is why you get the "*"
The FMTLIB option will print information on the format created.
Bruno
Thanks Bruno, your solution works.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.