Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

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

[ Edited ]

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;


Accepted Solutions
Solution
Tuesday
PROC Star
Posts: 172

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

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;

View solution in original post


All Replies
Solution
Tuesday
PROC Star
Posts: 172

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

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;

Contributor
Posts: 21

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

Sorry, forgot to mention, can it be done within the PROC SQL statement?
Super User
Posts: 10,516

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

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.

 

Contributor
Posts: 21

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

I want to change the value within the dataset. I have updated my code in original post.
PROC Star
Posts: 172

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

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

Contributor
Posts: 21

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

Please see updated codes above.
Super User
Posts: 10,516

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


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

Contributor
Posts: 21

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 244 views
  • 3 likes
  • 3 in conversation