topic Re: word NULL in dataset in SAS Programming

word NULL in dataset

AP101 — Thu, 13 Sep 2018 17:49:06 GMT

I have a dataset that has the word NULL as a value and I am trying to replace it with a blank space but it doesn't work with the below IF statement. How can I overwrite/replace the word NULL?

IF TSA_CODE_1 = 'NULL' THEN TSA_CODE_1 = '';

Re: word NULL in dataset

AMSAS — Thu, 13 Sep 2018 17:48:51 GMT

Hi,

That should work, unless your variable TSA_CODE_1 isn't what it appears to be from this code.

Here's an example, note how a & c are both strings, whereas b is a numeric.

Reduce your code down to run a similar test and check the log for NOTES/WARNINGS & ERRORS

data t ;
	a='NULL' ;
	b=. ;
	c='NULL' ;
	if c='NULL' then
		c='' ;
run ;
proc print data=t ;
run ;

Re: word NULL in dataset

novinosrin — Thu, 13 Sep 2018 17:54:46 GMT

I prefer call missing instead of ' ' or .


data t ;
	a='NULL' ;
	b=. ;
	c='NULL' ;
	
run ;

data want;
set t;
if c='NULL' then call missing(c) ;
run;

Re: word NULL in dataset

AP101 — Thu, 13 Sep 2018 18:08:15 GMT

That worked to remove the NULL values and the 6 digit code string that is also in the column. I want to maintain the code string but just remove any instances of the word NULL in the same field. How can I do this?

TSA_CODE_1

NULL

061002

071004

101002

NULL

Re: word NULL in dataset

Vibcom — Thu, 13 Sep 2018 18:26:25 GMT

As we all know ,Null is a char value. So you can remove it using either if condition or with call missing--
1. data test;
input x1 $;
if X1='null' then x1='';
cards;
A1
null
A2
;
run;

2. data test;
input x1 $;
if X1='null' then call missing(x1);
cards;
A1
null
A2
;
run;

Re: word NULL in dataset

SuryaKiran — Thu, 13 Sep 2018 18:32:48 GMT

Check if leading or trailing blanks are causing the issue.

IF strip(TSA_CODE_1) = 'NULL' THEN TSA_CODE_1 = '';

Re: word NULL in dataset

AP101 — Thu, 13 Sep 2018 18:45:39 GMT

Thanks! Adding the STRIP worked. I now have:

TSA_CODE_1

061002
071004
101002