Numeric variable with hidden "character" values

[slangan]

Please read post before replying.

I was cleaning a SAS dataset of laboratory values when I found hidden values in a numeric variable. FASTGLU is a numeric value with a format of BEST12. After I initially attempted to remove all missing values (if FASTGLU^=.;), I found there were still missing values left in the dataset when I ran proc means. After removing all of the valid values,  I found these remaining missings values via the table editor, they appeared as "T"s (and one "U"). I double checked that FASTGLU is a numeric variable. I tried to run proc freq and proc means on these values, but the values behave as missings. I tried removing the variable format (by proc databases)  to see if there is a certain value associated with "T" or "U" (no luck). I used the SAS export wizard to export the data to Excel to find the values. When exported, FASTGLU is a column of blank cells.  The column label for FASTGLU is  "Fasting serum glucose (mg/dL, .T if not fasting)" , so it's a  programming trick with limited use. I can visually see from FASTGLU=T that there should be a nonfasting glucose available but I can't program "if FASTGLU=T then GLUCOSE=NONFASTGLU"  because SAS recognizes FASTGLU=T as a missing value. I'm just curious about this situation.

Here's my code:

proc datasets lib=work noprint;

modify GluCheck;

attrib _all_ label=' ';

attrib _all_ format=;

run;

proc means data=GluCheck noprint; /*Get Min/Max of Valid Values*/

var FASTGLU;

output out=GluStat (drop=_TYPE_ _FREQ_) Min=Gmin Max=Gmax;

run;

/*Add Min/Max to Glucose data*/

proc sql;

create table GluAddStat as

select * from

GluCheck, GluStat;

quit;

data JustGlu (drop=Gmin Gmax);

set GluAddStat (where=(if FASTGLU^=. )); /*drop missing values*/;

if (FASTGLU < Gmin or FASTGLU > Gmax); /*drop values between min and max*/

run;

proc means data=JustGlu n mean min max nmiss;

var FASTGLU;

title 'FASTGLU - Remaining Missing Values';

run;

proc freq data=JustGlu;

tables FASTGLU;

title 'FASTGLU - Remaining Missing Values';

run;

[art297 · Accepted solution]

Sounds like you have missing values of .T and .U

.A thru .Z are all valid missing values as us ,

Art, CEO, AnalystFinder.com

[PaigeMiller]

These are also missing values, represented as .T or .U in SAS code, and shown as T or U in the viewtable.

Instead of if FASTGLU^=.; you want to use

if not missing(fastglu);

[ballardw]

The special missing have uses. Here is one example:

data example;
   input x;
   if x= 6 then x=.D;
   if x= 7 then x=.R;

datalines;
1
2
3
4
5
6
7
;
run;

proc format library=work;
value MyDr
.D="Don't Know"
.R="Refused";
run;
proc print data=example;
   format x MyDR.;
run;
Proc freq data=example;
  tables x;
run;

The data step simulates reading an external data set with specific code values that would be considered missing for most analysis. Then use of the custom format allows creating report text to explain why the spefic values are missing.