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 ;
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?
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
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.
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.
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?
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.