Missing values not allowing computation

[Blndbrm727]

Code:
/*Counting singular drugs for polysub count*/
data drugtype2;
	set YTDtotal3;
If Fent=1 then FentC=1;
If Opiate=1 then OpiateC=1;
If Oxy=1 then Oxyc=1;
If Methd=1 then Methdc=1;

PolyO=sum(Fentc,Opiatec,Oxyc,Methdc);

Log: 
  /*Counting singular drugs for polysub count*/
926  data drugtype2;
927      set YTDtotal3;
928  If Fent=1 then FentC=1;
929  If Opiate=1 then OpiateC=1;
930  If Oxy=1 then Oxyc=1;
931  If Methd=1 then Methdc=1;
932  PolyO=sum(Fentc,Opiatec,Oxyc,Methdc);
933  run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      407 at 932:7
NOTE: There were 703 observations read from the data set WORK.YTDTOTAL3.
NOTE: The data set WORK.DRUGTYPE2 has 703 observations and 39 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


934
935  proc freq data=drugtype;
936      tables polyo*fentc*opiatec*oxyc*methdc/list missing nopercent;
937      title 'polyo qa check';
938  run;

NOTE: There were 1 observations read from the data set WORK.DRUGTYPE.
NOTE: PROCEDURE FREQ used (Total process time):
      real time           0.06 seconds
      cpu time            0.03 seconds

Hi All,

Above is my code and log. I have checked my proc contents and all variables are there. I am not sure why missing variables are causing a problem here. Any help or insight is greatly appreciated. Thanks!

[VDD]

have you confirmed that you have valid values in all of your variables you are setting the conditions for?

see what results you get when you run this code

proc freq data=YTDtotal3;
	tables Fent Opiate Oxy Methd / missing;
quit;

[Blndbrm727]

Fent 
Fent Frequency Percent Cumulative Frequency Cumulative Percent 
.     8 1.14 8 1.14 
0 69 9.82 77 10.95 
1 242 34.42 319 45.38 
2 337 47.94 656 93.31 
3 22 3.13 678 96.44 
4 1 0.14 679 96.59 
7 24 3.41 703 100.00 



Opiate 
Opiate Frequency Percent Cumulative Frequency Cumulative Percent 
. 9 1.28 9 1.28 
0 264 37.55 273 38.83 
1 86 12.23 359 51.07 
2 317 45.09 676 96.16 
3 25 3.56 701 99.72 
4 2 0.28 703 100.00 



OXY 
OXY Frequency Percent Cumulative Frequency Cumulative Percent 
. 11 1.56 11 1.56 
0 167 23.76 178 25.32 
1 21 2.99 199 28.31 
2 176 25.04 375 53.34 
3 20 2.84 395 56.19 
7 308 43.81 703 100.00 



Methd 
Methd Frequency Percent Cumulative Frequency Cumulative Percent  
. 9 1.28 9 1.28 
0 288 40.97 297 42.25 
1 56 7.97 353 50.21 
2 322 45.80 675 96.02 
3 25 3.56 700 99.57 
4 2 0.28 702 99.86 
7 1 0.14 703 100.00 

[PaigeMiller]

As implied by @VDD, but a more broad answer.

Whenever SAS doesn't give you the expected answers, you need to look at the SAS data set WITH YOUR OWN EYES. So open data set DRUGTYPE2 in viewtable, or do a PROC PRINT, and do you see missing values? Looking at the data is highly recommended as a debugging tool, and can solve a lot of problems quickly. Whatever data you posted above is not the same as looking at the SAS data set, to see if SAS has read it properly. You need to look at SAS data set DRUGTYPE2 with your own eyes, looking at the RAW data is not the same!

Also, when you do PROC FREQ on data set DRUGTYPE, this tells you nothing about data set DRUGTYPE2. Perhaps you have a typographical error in your program?

[Blndbrm727]

Question: Why would the missing values come into play here as I am only asking SAS about values=1? WOuldn't SAS just skip over the missing the values?  This file is the compilation of monthly files of Jan-April and this code worked in each monthly file so am perplexed why it is now not working in the compilation file...

[Kurt_Bremser]

data drugtype2;
    set YTDtotal3;
If Fent=1 then FentC=1;
If Opiate=1 then OpiateC=1;
If Oxy=1 then Oxyc=1;
If Methd=1 then Methdc=1;
PolyO=sum(Fentc,Opiatec,Oxyc,Methdc);
run;

In all cases where the values are not equal to 1, you will have missing values. Looking at your proc freq results, it is not a surprise that more than half of the observations will result in 4 missing values as arguments to the sum() function, which then will also deliver missing as result. That's all.

Maxim 3: Know Your Data.

[Astounding]

FentC is 1 or missing (not 1 or 0). Same for the other variables.

If the SUM function processes all missing values, it returns a missing value, along with the note that you see in the log.

[Blndbrm727]

Thanks all. I just tried setting the missing values for each variable to 9 and that did not work either. Still got a missing value in the sum. 😞

[PaigeMiller]

Can you look at this exact SAS data set DRUGTYPE2 with your own eyes and see the possible cause of this problem? (And we don't want you to look at the original data, nor the file you read the data from, nor some other SAS data set, but this exact SAS data set DRUGTYPE2)

Can you show us this exact SAS data set DRUGTYPE2, so we can see the values in this exact SAS data set, using this method (and not any other method)?

Can you address the issue that your PROC FREQ is performed on data set DRUGTYPE while your math is being performend on data set DRUGTYPE2?

[Kurt_Bremser · Accepted solution]

---

@Blndbrm727 wrote:

Thanks all. I just tried setting the missing values for each variable to 9 and that did not work either. Still got a missing value in the sum. 😞

---

That's not the problem. You only test for value 1, so all other values in X will cause the Xc variable to be left missing. You should do

if fent = 1 then fentc = 1 else fentc = 0;

or

fentc = ifn(fent=1,1,0);

[Blndbrm727]

Hi Kurt

I will try that again. I tried it earlier and it still gave me missing in the sum values. Be right back. Fingers crossed! 

[Blndbrm727]

559  *qa;
560  proc freq data=drugtype;
561      tables PolyO*FentC*OpiateC*Oxyc*Methdc/list missing nopercent;
ERROR: Variable POLYO not found.
ERROR: Variable FENTC not found.
ERROR: Variable OPIATEC not found.
ERROR: Variable OXYC not found.
ERROR: Variable METHDC not found.
562      title 'polyo qa check';
563  run;

Making progress. Not sure why I am getting this as when I did proc contents all those variables are listed! At least I am not getting missing any more! 🙂

[Blndbrm727]

DUH!! SORRY I forgot to get accurate dataset. It should be drugtype2!

[Blndbrm727]

Now it works! Thank you everyone!!