BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
slangan
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
art297
Opal | Level 21

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
Diamond | Level 26

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

 

--
Paige Miller
ballardw
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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