topic Re: How do I replace all values for a variable with blanks or null in SAS Programming

How do I replace all values for a variable with blanks or null

cc15 — Tue, 11 Jun 2024 20:01:34 GMT

I have a very large dataset and I am trying to replace several numeric and character variable values (trying to null all of the values with certain variables) with either a blank or null (.). I tried the following code but got several error messages. Is this the best code or are there others?

proc iml;
edit mp_;
read all var (Result1) where (Result1 ^= '0');
Result1 = ' ';
replace all var (Result1) where (Result1 ^= '0');
run;

4 proc iml;
NOTE: Writing HTML Body file: sashtml.htm
NOTE: IML Ready
5 edit mp_;
6 read all var (Result1) where (Result1 ^= 0);
ERROR: Operand Result1 does not have a value.

statement : READ at line 6 column 1
7 Result1 = ' ';
8 replace all var (Result1) where (Result1 ^= 0);

ERROR: An exception has been encountered.

Re: How do I replace all values for a variable with blanks or null

Mazi — Tue, 11 Jun 2024 20:15:35 GMT

Hi, in the data step you can use the call missing call routine.
e.g.

data test;
	set sashelp.class;
	if _n_ in (1 3 5 7 9) then call missing (of _all_); /*clear all variables*/
	else if _n_ in (2 4 6 8) then call missing (of name--height); /*clear variables between name and height, inclusive*/
	else call missing(of height weight); /*clear only height and weight*/
run;

Hope it helps

Re: How do I replace all values for a variable with blanks or null

cc15 — Tue, 11 Jun 2024 20:38:43 GMT

I tried the following code recommended, but the variables were not nulled

data mp_test;
set mp_;
if Result1 in ("Positive" "Unknown") then call missing (of _all_);
run;

Re: How do I replace all values for a variable with blanks or null

cc15 — Tue, 11 Jun 2024 20:51:11 GMT

This version worked, thanks for the guidance

data mp_test;
set mp_;
call missing (Result1);
run;

Re: How do I replace all values for a variable with blanks or null

Mazi — Wed, 12 Jun 2024 04:53:16 GMT

I’m curious to understand why this did not work for you.

Are you sure the values passed to the in operator are correct ? That is, that they the correctly cased since strings are case-sensitive, and spelled correctly, exactly as they appear in your data ?

Re: How do I replace all values for a variable with blanks or null

cc15 — Thu, 13 Jun 2024 19:38:34 GMT

I think that code was too specific, I was looking to null all of the values.

Re: How do I replace all values for a variable with blanks or null

Tom — Fri, 14 Jun 2024 01:20:48 GMT

@cc15 wrote:

I think that code was too specific, I was looking to null all of the values.

The CALL MISSING(of _ALL_) is going to set ALL of the VARIABLES to missing but only on the observations where it runs.

To set the variable to missing on every observation then do not use the IF/THEN.

Or simpler still just DROP the variable, since a variable that is missing on every observation is not adding much information to the dataset and just just taking up space.