05-27-2016 07:12 AM - edited 05-27-2016 07:14 AM
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
if ci_id =. then ci_id1="NULL" ;
05-27-2016 07:18 AM
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?
05-27-2016 07:32 AM
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
05-27-2016 07:41 AM
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.
05-27-2016 07:44 AM
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.
05-27-2016 07:48 AM
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?
05-27-2016 07:25 AM
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:
value missnull .='NULL';
proc print data=test.t_eticket;
format ci_id missnull.;
That will change what prints, without changing the actual value of the variable.
05-27-2016 07:52 AM
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.