Set all missing values to NULL

Reply
Occasional Contributor
Posts: 11

Set all missing values to NULL

[ Edited ]

 

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 ;

Grand Advisor
Posts: 17,325

Re: Set all missing values to NULL

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?

Occasional Contributor
Posts: 11

Re: Set all missing values to NULL

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

 

Respected Advisor
Posts: 4,969

Re: Set all missing values to NULL

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.  

Esteemed Advisor
Posts: 6,646

Re: Set all missing values to NULL

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 6,320

Re: Set all missing values to NULL

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?

Respected Advisor
Posts: 4,969

Re: Set all missing values to NULL

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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Set all missing values to NULL

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. 

Ask a Question
Discussion stats
  • 7 replies
  • 466 views
  • 0 likes
  • 6 in conversation