topic Re: How to change a specific string value in the entire data set? in SAS Enterprise Guide

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

KubiK888 — Fri, 11 Aug 2017 22:29:35 GMT

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;

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

novinosrin — Fri, 11 Aug 2017 21:58:23 GMT

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;

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

KubiK888 — Fri, 11 Aug 2017 22:01:02 GMT

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

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

ballardw — Fri, 11 Aug 2017 22:07:45 GMT

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.

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

KubiK888 — Fri, 11 Aug 2017 22:10:04 GMT

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

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

novinosrin — Fri, 11 Aug 2017 22:23:15 GMT

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

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

KubiK888 — Fri, 11 Aug 2017 22:30:02 GMT

Please see updated codes above.

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

ballardw — Fri, 11 Aug 2017 22:52:51 GMT

@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_;

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

KubiK888 — Fri, 11 Aug 2017 22:56:49 GMT

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