BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KubiK888
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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
Calcite | Level 5
Sorry, forgot to mention, can it be done within the PROC SQL statement?
ballardw
Super User

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
Calcite | Level 5
I want to change the value within the dataset. I have updated my code in original post.
novinosrin
Tourmaline | Level 20

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

KubiK888
Calcite | Level 5
Please see updated codes above.
ballardw
Super User

@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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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