null characters?

[me55]

i run into this every now and then and i need help with it because i do not get it.  i have a cell in a column that has no data apparent in it so it must be null or have a null character or something in it.  i need that cell not to be null.  i run

data TABLE1;
  set TABLE1;
  	FIELD1=strip(FIELD2);
	FIELD2=strip(FIELD2);
run;

to clear any data/null characters and remove any spaces in front or behind.  so any field should have, if that is working, only nothing or be null, if that is not working, then it will have a null character.  so i run to update...

data TABLE1;
  set TABLE1;
  	if length(FIELD2)<=3 or FIELD2=" " then
	do;
	FIELD2=catx('','0000',strip(ID));
	end;
run;

where ID is just an auto-number field. however it does not update that stupid field.  i have also tried in the place of FIELD2, missing(FIELD2), FIELD2=. and i cannot get that field to update to id. but when i run...

proc sql;
select FIELD0, FIELD1, FIELD2
	from TABLE1
	where FIELD2=' ';
quit; 

or 

proc sql;
select FIELD0, FIELD1, FIELD2
	from TABLE1
	where FIELD2 is NULL;
quit;

that field comes up. 

can someone help me out with that is going on here and how i can get that to pull in a data step?  i dont get it...

[Reeza]

Try using CALL MISSING(variable) to set your missing values and MISSING() functions instead of using NULL Or checking against a character value.

[me55]

sas did not like it...

data TABLE1;
  set TABLE1;
  	if length(FIELD2)<=3 or call missing(FIELD2) then
	do;
	FIELD2=catx('','0000',strip(ID));
	end;
run;

so that did not work. 

[Reeza]

1. What's the type of the variable FIELD, numeric or character?
2. CALL MISSING() sets values to missing
3. MISSING() checks if a value is missing.

Can you show an example of what you have and what you want? Seems like you're trying to add leading zeroes which is much easier with PUT/INPUT() and Z5 format for example.

[me55]

it is a character field.  i am trying to find null fields and put a value in them.  i cannot have null fields.  i am just trying to add a few leading zeroes and the output of an autonumbered field as a substitute for no data.  every cell needs data at least 4 characters long. 

[Reeza]

if missing(variableName) then variableName = "XXXX";

That should be all that's needed, can you try that approach?

[me55]

that did not work.  that is what i did first.  there were 2 null looking fields.  that updated one of them however the other is still null (or whatever). 

also i tried...

data TABLE1;
  set TABLE1;
  call missing(FIELD2);
run;


data TABLE1;
  set TABLE1;
  	if length(FIELD2)<=3 or missing(FIELD2) then
	do;
	NNBR=catx('','0000',strip(ID));
	end;
run;

and that set every field to null then filled some with data.  ie it did not work. 

[Reeza]

When you use the same input and output data set names that becomes really hard to follow and it's easy to get buggy code. Is that your actual code? Can you show the log as well.

And can you display teh value that isn't found as missing but you think is missing using a HEX format?

If not, try COMPBL() with the options to remove any extraneous white space.

[Tom]

---

@me55 wrote:

that did not work.  that is what i did first.  there were 2 null looking fields.  that updated one of them however the other is still null (or whatever). 

also i tried...

data TABLE1;
  set TABLE1;
  call missing(FIELD2);
run;


data TABLE1;
  set TABLE1;
  	if length(FIELD2)<=3 or missing(FIELD2) then
	do;
	NNBR=catx('','0000',strip(ID));
	end;
run;

and that set every field to null then filled some with data.  ie it did not work. 

---

Hopefully you have a backup of TABLE1 (or a way to recreate it) since both of those steps are overwriting the input dataset.

Your first step is forcing all values of FIELD2 to be missing.

Your second step is assuming that FIELD2 is a character string and testing IF the length of the value of FIELD2 is less than 4 characters. (or missing but the length function will return 1 when a character field is missing so the second test is redundant.)  and if so then it is changing the value of some other CHARACTER field named NNBR. 

If the goal is to replace missing values then why are you testing one variable and replacing the value of a different variable?

Also note that this code will really NOT work right if any of those variables are numeric instead of character.

Not sure why you are using CATX() when you don't actually want to insert anything between the values.  Also the variable NNBR needs to be longer than four characters or to be able to hold the four zeros and the value of ID.