topic output proc report: No zeros in SAS Programming

output proc report: No zeros

Konkordanz — Tue, 10 Aug 2021 11:51:32 GMT

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!

Re: output proc report: No zeros

tarheel13 — Tue, 10 Aug 2021 12:14:47 GMT

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 '-'.

Re: output proc report: No zeros

PaigeMiller — Tue, 10 Aug 2021 12:50:50 GMT

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.

Re: output proc report: No zeros

Konkordanz — Wed, 11 Aug 2021 08:47:43 GMT

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;

Re: output proc report: No zeros

Patrick — Wed, 11 Aug 2021 09:02:27 GMT

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;

Re: output proc report: No zeros

Kurt_Bremser — Wed, 11 Aug 2021 09:28:52 GMT

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.

Re: output proc report: No zeros

Konkordanz — Thu, 12 Aug 2021 09:15:59 GMT

@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.;

Re: output proc report: No zeros

andreas_lds — Thu, 12 Aug 2021 09:18:18 GMT

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

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

Re: output proc report: No zeros

Konkordanz — Thu, 12 Aug 2021 09:47:51 GMT

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