topic Re: checking null in SAS Programming

checking null

woo — Fri, 10 Jan 2014 20:04:47 GMT

Hi all -

i need help and wonder what is the best way to check null values in sas datasets for all variables? and if exist then create index variable for it and then may be use Where statement to pull out...

Thanks!

Re: checking null

Reeza — Fri, 10 Jan 2014 20:17:05 GMT

What are you actually trying to do here? Get all records where any variable is null or missing?

Re: checking null

woo — Fri, 10 Jan 2014 20:45:46 GMT

I am sorry Reeza i am new at SAS so sometime i don't know how to explain it...BUT

i want get records for any null value in variable and then create index for that variable...

thanks!

Re: checking null

Reeza — Fri, 10 Jan 2014 20:49:53 GMT

Post some sample data and sample output please. Also clarify what you mean by index.

I could guess but if I was wrong this whole thing would repeat, so I'd rather not.

Re: checking null

Tom — Fri, 10 Jan 2014 21:11:20 GMT

If you want to find the observations that have missing values for a particular variable then you can use the WHERE statement. You can use it in any PROC.

proc means data=have ;

where age=. ;

var ht wt ;

run;

Re: checking null

woo — Tue, 14 Jan 2014 01:25:07 GMT

Hi guys - i think i can do this and it will run query fast from bigger data....

proc dataset library=woo;

modify=test /*dataset*/;

index create age;

index create sex;

quit;

data xyz;

set test;

where age=20 or 50 and sex='m';

run;

Thanks!!!

Re: checking null

LinusH — Tue, 14 Jan 2014 09:34:00 GMT

Not following you, now you are not testing for null....?

Besides, I think at least the Base engine will have problems optimize (use the index) your example query.

Creating a composite index using the SPDE engine could work (not sure).

Re: checking null

Tom — Tue, 14 Jan 2014 14:37:02 GMT

SAS will attempt to use the index if it is available. You can set option MSGLEVEL=I (that's the letter i for information) to have SAS report index usage.

Your WHERE clause syntax is wrong.

where (age=20 or age=50)

and (sex='m')

;

Re: checking null

sas_9 — Fri, 17 Jan 2014 00:32:16 GMT

you can try this i guess....

/*this way you will get missing & not missing value for all variables*/

proc format;

value $missfmt ' ' ='missing' other=' not missing';

value missfmt . = 'missing' other='not missing';

run;

proc format data= work.test; /*your test dataset goes here*/

format _char_ $missfmt. ;

table _char_ /missing nocum nopercent missprint;

format _numeric_ missfmt.;

table _numeric_/missing nocum nopercent missprint;

run;

/****gd lk!!!***/