Treating a missing value as zero for a calculated field

Reply
Contributor
Posts: 41

Treating a missing value as zero for a calculated field

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,

P.

Super Contributor
Posts: 1,636

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

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

Respected Advisor
Posts: 2,655

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

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

Contributor
Posts: 41

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

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

Ask a Question
Discussion stats
  • 3 replies
  • 200 views
  • 1 like
  • 3 in conversation