BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mgorripati
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

View solution in original post

2 REPLIES 2
BrunoMueller
SAS Super FREQ

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

mgorripati
Obsidian | Level 7

Thanks Bruno, your solution works.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1228 views
  • 1 like
  • 2 in conversation