topic Re: How to set all missing values to 0 in SAS Programming

How to set all missing values to 0

Ngo99 — Sat, 14 May 2022 13:48:33 GMT

hi, I am trying to set all missing values in the last_review column to 0. I tried to use this code however I am getting a date which is 01/01/1960 when I am expecting to get the value 0. How do I change the date given to put 0.

code:

proc sql;
update WORK.IMPORT
set
last_review = 0
where
last_review is missing;
quit;

Re: How to set all missing values to 0

Kurt_Bremser — Sat, 14 May 2022 13:56:25 GMT

A value of 0 in a variable used to store dates will result in 1960-01-01, as that is day zero on the SAS date scale.

"Unknown" must be stored as a missing value.

Re: How to set all missing values to 0

PaigeMiller — Sat, 14 May 2022 14:38:24 GMT

Good advice from @Kurt_Bremser

In addition:

Changing missing values to an actual number (without good reason) is something to be avoided. It makes mathematical operations — such as taking an average — incorrect. And of course, usually, it is extra work that provides no benefit, something else to be avoided.

Re: How to set all missing values to 0

Ngo99 — Sat, 14 May 2022 15:32:27 GMT

thank you

Re: How to set all missing values to 0

ballardw — Sun, 15 May 2022 08:29:42 GMT

Hint: Missing values are easy to display in reports by using the options statement:

Options missing='0';

This will, until reset, display all missing values as 0 instead of . in output such as Procs Report or Print.

Another is to use a custom format. And example for your variable, since it is apparently a date value might look like:

proc format;
value mycustomdate
. = "N/A"
other = [date9.]
;
run;

and use the Mycustomdate format instead of the current one. Use your current date format in the [ ] brackets above if using something other than date9. You can display any text you want for the missing values though I would keep it short and relevant to the dates use. The brackets coupled with the keyword Other in Proc format means all values not in another value range are displayed with the format in the brackets.