BookmarkSubscribeRSS Feed
goms
Fluorite | Level 6

 

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 ;

7 REPLIES 7
Reeza
Super User

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?

goms
Fluorite | Level 6

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

 

Astounding
PROC Star

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.  

Kurt_Bremser
Super User

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.

Tom
Super User Tom
Super User

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?

Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 12191 views
  • 0 likes
  • 6 in conversation