How to change a specific string value in the entire data set?

[KubiK888]

Say, I want to change string "missing" into acutal missing "". I know how to do it for a single variable, but how can I do it for an entire working dataset?

.........................................

PROC SQL;
CREATE TABLE WORK.SOME_DATA_01 AS
SELECT t1.someid,

(CASE
WHEN 'missing' = t1.postal_code THEN ''
ELSE t1.postal_code)
END) LABEL="Postal_code" AS postal_code_v2

etc

FROM WORK.TABLE t1;

QUIT;

[novinosrin · Accepted solution]

Do you mean something like this is what you are after?

data want;

set have;

array s _char_;

do _n_=1 to dim(s);

if s(_n_)="missing" then call missing(s(_n_));

end;

run;

[KubiK888]

Sorry, forgot to mention, can it be done within the PROC SQL statement?

[ballardw]

How about without even changing the data? You can set a format to display just about any value you have in a desired form without modifying data.

Proc format library=work;
value $missing (default=25)
"missing"=' '

;
run;

data example;
   input text $;
datalines;
Some
words
are 
missing
how
about
that
;
run;

proc print data=example noobs;
   format text $missing.;
run;
   

the default setting should be large enough to display other expected values.

[KubiK888]

I want to change the value within the dataset. I have updated my code in original post.

[novinosrin]

Can you share the sql code of yours that you did for one variable please?

[KubiK888]

Please see updated codes above.

[ballardw]

---

@KubiK888 wrote:
I want to change the value within the dataset. I have updated my code in original post.

---

With your question "Say, I want to change string "missing" into acutal missing "". I know how to do it for a single variable, but how can I do it for an entire working dataset?"

Are you actually asking how to do this to many different varaibles within a data set?

This is really much easier in a data step with an Array, the basic tool for doing the same thing to mulitple variables:

data want;

    set have;

    array _m_ textvar1 textvar3 othertextvar;  /* list of variables to modify after the array name "_m_". If you have a variable named _m_ use something else*/

    do i = 1 to dim(_m_);

       if _m_[i] = 'missing' then call missing( _m_[i] );

    end;

    drop i;

run;

Other wise you will have either type out all the case statments or wander into macro language and add complexity to a simple task.

And to make the above even simpler if you want to do this to every single character varaible in the dataset then define the array as:

array _m_  _character_;

[KubiK888]

I thought it might be easier solution in PROC SQL, I will try yours next week. Thanks.