topic Re: Treating a missing value as zero for a calculated field in SAS Programming

Treating a missing value as zero for a calculated field

pmdci — Wed, 23 Jan 2013 20:31:33 GMT

Hello there,

I have a dataset with two variables: funding Year1 and funding Year2

I want to create a third variable, which would hold the calculated different between Year1 and Year2:

Diff_Y1Y2 = Year1 - Year2;

However, I noticed that if either Year1 or Year2 has a missing value, the value of the calculated Diff_Y1Y2 will also be missing:

198267 - NULL = NULL;

NULL - 198267 = NULL;

I was hoping that for the sake of the calculation, the value could be treated as zero. Is that possible?

Best wishes,

Re: Treating a missing value as zero for a calculated field

Linlin — Wed, 23 Jan 2013 20:36:00 GMT

example:

data have;
input v1 v2;
cards;
2 .
7 5
. 6
. .
;
data want;
set have;
diff=sum(v1,-v2,0);
proc print;run;
Obs v1 v2 diff

                      1      2     .      2
                      2      7     5      2
                      3      .     6     -6
                      4      .     .      0

Message was edited by: Linlin

Re: Treating a missing value as zero for a calculated field

SteveDenham — Wed, 23 Jan 2013 20:38:33 GMT

While it is certainly possible to program that to occur, the bigger question is "Why?" Why set an indeterminant value to zero? If you have a good business rule for doing so, then go for it (code below). But if it is a matter that perhaps the data was not observed/collected for a given year, you shouldn't set the value to zero, because you shouldn't assume that the funding was identical in both years.

Anyway, code:

if (.<year1 and .<year2) then diff_y1y2=year1 - year2;

else diff_y1y2=0;

Steve Denham

Added upon rereading the OP:

I may have misunderstood you. If you meant to set the missing INPUT value to zero, then pay no attention to my ranting. My concern was that you wanted to set the OUTPUT (the difference) to zero if one of the inputs was missing.

Message was edited by: Steve Denham

Re: Treating a missing value as zero for a calculated field

pmdci — Tue, 23 Apr 2013 11:15:38 GMT

I meant the missing input to zero I do agree with the rant, though. Still some people think that NULL = 0