output proc report: No zeros

[Konkordanz]

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!

[tarheel13]

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]

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.

[Konkordanz]

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]

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]

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]

@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 · Accepted solution]

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

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

[Konkordanz]

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