Replacing missing character variable

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Replacing missing character variable

Hello wise people,

I have few character variables that have YES, NO or . characters. The missing values for these characters are shown on the dataset as dot (.)

I am trying to convert those dots (.) to NO and running into some issues and it returns with above 3 values in proc freq even after coding them to convert to NO in the data statement.

If I try these commands:

if postretrobleed=' ' then postretrobleed='No';

or alternatively,

if postretrobleed='.' then postretrobleed='No';


it still comes with YES, NO and . values for this variable.

I am not sure what I am doing wrong or what else I could try.

Does anyone know this?

Your response is much appreciated!

Thanks,

Venki


Accepted Solutions
Solution
‎03-31-2013 09:06 PM
Respected Advisor
Posts: 4,651

Re: Replacing missing character variable

If data_null_'s guess is right then

if STRIP(postretrobleed) = '.' then postretrobleed = 'No';

would do the trick.

PG

PG

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,743

Re: Replacing missing character variable

Hi:

  Are there any error or warning messages in the LOG? Does your new dataset get created without errors? Do you use the new dataset name in the PROC FREQ step instead of the original dataset name? Without seeing more of your code and your data, it is impossible to comment. For example, when I run this test program using SASHELP.CLASS, with character variables, my assignment statement works.

cynthia

data fakedata;
  length postretrobleed $3;
  set sashelp.class;
  ** make some values for postretrobleed variable based;
  ** on values for SEX and HEIGHT;
  postretrobleed='Yes';
  if sex = 'M' and height le 62 then postretrobleed = '.';
  else if sex = 'F' and height gt 60 then postretrobleed=' ';
  else if sex = 'F' then postretrobleed = 'No';
run;
    
ods listing;
proc freq data=fakedata;
  title '1) Before "fix" of variable';
  tables postretrobleed/  missing;
run;
   
data fixdata;
  set fakedata;
  if postretrobleed in (' ', '.') then postretrobleed='No';
run;
  
ods listing;
proc freq data=fixdata;
  title '2) After "fix" of variable';
  tables postretrobleed/  missing;
run;

Contributor
Posts: 21

Re: Replacing missing character variable

Thanks for your reply!

I am using the same file name as it was created.

The dataset was created by combining multiple (8 datasets) sometime ago and I remember that log during that time and in this current combined dataset does mention this message in the log.

NOTE: Character values have been converted to numeric values at the places given by:

      (Line)Smiley SadColumn).

      130:19

And it goes on providing the full description of that observation (i.e it lists the entire 280 variable for that one observation)

similiarly, the log goes and describes multiple other observations that it did the same thing

I tried the

data fixdata;

  set fakedata;

  if postretrobleed in (' ', '.') then postretrobleed='No';

run;

 

It still returns YES NO and .

Is there anything else that I could try?

Thanks a lot for your reply.

Best,

Venki

PROC Star
Posts: 7,363

Re: Replacing missing character variable

: Did you try Data_Null's suggestion? If not, it might provide the solution you are looking for.  This takes it one step further, namely using strip():

data fakedata;

  length postretrobleed $3;

  set sashelp.class;

  ** make some values for postretrobleed variable based;

  ** on values for SEX and HEIGHT;

  postretrobleed='Yes';

  if sex = 'M' and height le 62 then postretrobleed = ' .';

  else if sex = 'F' and height gt 60 then postretrobleed='  ';

  else if sex = 'F' then postretrobleed = 'No';

run;

    

   

data fixdata;

  set fakedata;

  if strip(postretrobleed) in ('', '.') then postretrobleed='No';

run;

Contributor
Posts: 21

Re: Replacing missing character variable

Thanks for your reply. No, I tried looking it up how to use it. I am not sure how to use LEFT or STRIP function in my scenario. Anyways, I just left a reply to data_null

Thanks for your suggestion

Super Contributor
Posts: 644

Re: Replacing missing character variable

Venki

It looks to me as if your column is really numeric, rather than character, which would explain the persistence of the dot (numeric missing value).  You see values YES and NO because the data has a format which converts numeric values (possibly 0 and 1 or 1 and 2) to a character representation.

Try

Proc contents data = enter the dataset name here, including the libname if it is a two level name ;

run;

This should reveal the data structure.

If I'm right the following code might work

if postretrobleed=. then postretrobleed=0;

If this now shows values of YES, NO and 0 try another number instead of 0, maybe 2.

Richard

Respected Advisor
Posts: 3,777

Re: Replacing missing character variable

That DOT may not be in column 1.  Maybe a nice LEFT function whould help.

Contributor
Posts: 21

Re: Replacing missing character variable

Thanks for your reply. I am relatively new to SAS.

Could you tell how I could use LEFT or STRIP function for my scenario?

Best,

Venki

Solution
‎03-31-2013 09:06 PM
Respected Advisor
Posts: 4,651

Re: Replacing missing character variable

If data_null_'s guess is right then

if STRIP(postretrobleed) = '.' then postretrobleed = 'No';

would do the trick.

PG

PG
Contributor
Posts: 21

Re: Replacing missing character variable

Just awesome !!! Works like a charm.

Thank you so much

Venki

Respected Advisor
Posts: 4,651

Re: Replacing missing character variable

You are welcome. Please give the credit to @data_null_;  it was he who guessed what the problem was. - PG

PG
Contributor
Posts: 21

Re: Replacing missing character variable

Thanks a lot for your suggestion. The STRIP function worked really well !

Venki

☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 359 views
  • 8 likes
  • 6 in conversation