SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Numeric variable with hidden "character" values

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Numeric variable with hidden "character" values

[ Edited ]

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;

 


fasting_glucose.JPG

Accepted Solutions
Solution
‎05-15-2017 03:24 PM
PROC Star
Posts: 7,471

Re: Numeric variable with hidden "character" values

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

 

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

 

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
‎05-15-2017 03:24 PM
PROC Star
Posts: 7,471

Re: Numeric variable with hidden "character" values

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

 

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

 

Art, CEO, AnalystFinder.com

 

Trusted Advisor
Posts: 1,913

Re: Numeric variable with hidden "character" values

[ Edited ]

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);

 

Super User
Posts: 11,343

Re: Numeric variable with hidden "character" values

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 230 views
  • 0 likes
  • 4 in conversation