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

Hi Forum,

 

I have a SAS-Output with Zeros in serval cells. These are the result of a formular many steps earlier. But: I dont want to get these Zeros in my output. I actually want SAS to do: If the result=zero then give me a "-" in this cell.

Is it for example in the output-option doable?

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

You can attach only one format to a variable / column. Try:

proc format;
  value nozero 
    0, . ='-' 
    other=[best10.2];
run;

View solution in original post

8 REPLIES 8
tarheel13
Rhodochrosite | Level 12

Are your variables numeric or character? I usually made everything character for proc report. Make a data step before proc report and convert the character variable to '-'.

PaigeMiller
Diamond | Level 26

A custom format can be used to make 0 appear differently.

 

Example:

 

proc format;
    value nozero 0='-' other=[best10.2];
run;

and then assign this format to the variable in the PROC REPORT DEFINE statement.

 

--
Paige Miller
Konkordanz
Pyrite | Level 9

Thank you both for your answers, good ideas.

@PaigeMiller : I tried also your way to understand the proc-format-thing. But it doesnt work. I tried this code, but where is the problem of my attemption?

 

data have;
input city $ price;
datalines;
Dresden 2005
Berlin 0
Frankfurt 0
Bonn 2
Berlin 4030
Bonn 0
Dresden 2005
Frankfurt 6053
Berlin 0
Frankfurt 0
Dresden 0
Bonn 0
Berlin 4030
Frankfurt 0
Bonn 3050
Dresden 0
;
run;

proc format;
    value $nozero 0='-' other=[best10.2];
run;

proc report data=have;
column city price;
define city / display;
define price / display format=$nozero;
run;
Patrick
Opal | Level 21

Your code just needs a small tweak to work.

1. Price is numerical so the format must be numerical (=format name without a dollar sign)

2. Calling a format requires a dot added to the format name

data have;
  input city $ price;
  datalines;
Dresden 2005
Berlin 0
Frankfurt 0
Bonn 2
Berlin 4030
Bonn 0
Dresden 2005
Frankfurt 6053
Berlin 0
Frankfurt 0
Dresden 0
Bonn 0
Berlin 4030
Frankfurt 0
Bonn 3050
Dresden 0
;
run;

proc format;
  value nozero 0='-' other=[best10.2];
run;

proc report data=have;
  column city price;
  define city / display;
  define price / display format=nozero.;
run;
Kurt_Bremser
Super User

Maxim 2: Read the Log.

This is your first clue:

 98         define price / display format=$nozero;
                                          _
                                          22
                                          200
 ERROR 22-322: Expecting ein Formatname.  
 ERROR 200-322: The symbol is not recognized and will be ignored.

OK, so $nozero is not recognized as a format. That is because format references always need a dot.

So add this dot:

 98         define price / display format=$nozero.;
 99         run;
 
 ERROR: price must use a numeric format.

and now you get the message about the numeric format.

 

The examples in the PROC REPORT documentation (like here) contain correct format references.

Maxim 1 is the first maxim for a reason.

Konkordanz
Pyrite | Level 9

@all: Thank you for your help, pretty helpful! It works! 🙂

But Ive one more question: In some cells the result is not 0 but empty. In the output it results in a point. But empty cells also should be a "-". I tried it with this code but it doesnt work. Could you please give me another hink?

 

 

proc format;
  value nozero 
		0='-' other=[best10.2];
  value nopoint
  		"" ="-" other=[best10.2];
run;

proc report data=...
[...] define Jahr_&Jahr1. / display "Data" style={cellwidth=5 cm} format=nozero.; format=nopoint.;
andreas_lds
Jade | Level 19

You can attach only one format to a variable / column. Try:

proc format;
  value nozero 
    0, . ='-' 
    other=[best10.2];
run;
Konkordanz
Pyrite | Level 9

Thank you all, this forum is very helpful and important for me!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 8 replies
  • 2080 views
  • 7 likes
  • 6 in conversation