topic Re: Set all missing values to NULL in SAS Programming

Set all missing values to NULL

goms — Fri, 27 May 2016 11:14:02 GMT

I want to replace all the missing values (.) to NULL

i tried the below one and also need to replace all the dot values in the particular field to be replaced as NULL

Below query is not working .please let me know how to modify this

data yourdata;
set test.t_eticket(drop=subject);
if ci_id =. then ci_id1="NULL" ;
run ;

Re: Set all missing values to NULL

Reeza — Fri, 27 May 2016 11:18:25 GMT

Are you interested in seeing the word NULL or looking for a Nukl value which would be a space?

Thats not how,SAS represents data, missing is equivalent to Null.

Why are you trying to do this?

Re: Set all missing values to NULL

Astounding — Fri, 27 May 2016 11:25:41 GMT

You can't store a set of characters in a numeric variable. But there are a couple of things you can try.

Special missing values:

if ci_id=. then ci_id=.N;

Now the missings will print as "N"

Or you could create and apply a format:

proc format;

value missnull .='NULL';

run;

proc print data=test.t_eticket;

format ci_id missnull.;

run;

That will change what prints, without changing the actual value of the variable.

Re: Set all missing values to NULL

goms — Fri, 27 May 2016 11:32:51 GMT

Hi,

Would like to see the value as NULL as the data come from source is empty string and loaded as NULL in HIve table

when i establish a connection with hive and execute the query in SAS .it shows dot value

End user wants to seet he result as an exact match with source

Re: Set all missing values to NULL

Astounding — Fri, 27 May 2016 11:41:21 GMT

That information is already lost by the time the data has arrived in SAS. The value of . means it is a missing value for a numeric variable, but it doesn't tell you what was in the Hive table. It might have been hex 00, or it might have been anything else under the sun that SAS can't interpret as a numeric.

Re: Set all missing values to NULL

Kurt_Bremser — Fri, 27 May 2016 11:44:25 GMT

When the data in hive is an empty string it is not numeric. It should then be imported into SAS as character, which would preserve the empty string.

Re: Set all missing values to NULL

Tom — Fri, 27 May 2016 11:48:11 GMT

If it is a string then why does it look like '.' in SAS? An empty string in SAS will just print as blank(s).

Is your variable actually a number? or has HIVE or SAS converted it to a number?

If it is a number then . is a missing (or what other systems would call null) value.

Why do you want to print is as the keyword NULL instead? Are you planning to transfer it back to some other system?

Re: Set all missing values to NULL

RW9 — Fri, 27 May 2016 11:52:27 GMT

Why? NULL in SAS terms would mean a string of 4 characters "NULL". Is does not mean an empty which is what you think it does. Remember each package has their own concepts.