topic Re: Replacing missing character variable in SAS Programming

Replacing missing character variable

Venki — Sun, 31 Mar 2013 17:56:23 GMT

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

Re: Replacing missing character variable

Cynthia_sas — Sun, 31 Mar 2013 18:30:09 GMT

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;

Re: Replacing missing character variable

data_null__ — Sun, 31 Mar 2013 18:44:31 GMT

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

Re: Replacing missing character variable

Venki — Sun, 31 Mar 2013 22:03:43 GMT

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):(Column).

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

Re: Replacing missing character variable

art297 — Sun, 31 Mar 2013 22:34:46 GMT

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

Re: Replacing missing character variable

Venki — Sun, 31 Mar 2013 23:12:53 GMT

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

Re: Replacing missing character variable

Venki — Sun, 31 Mar 2013 23:14:35 GMT

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

Re: Replacing missing character variable

RichardinOz — Mon, 01 Apr 2013 00:06:45 GMT

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

Re: Replacing missing character variable

PGStats — Mon, 01 Apr 2013 01:06:07 GMT

If data_null_'s guess is right then

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

would do the trick.

Re: Replacing missing character variable

Venki — Wed, 03 Apr 2013 15:08:54 GMT

Just awesome !!! Works like a charm.

Thank you so much

Venki

Re: Replacing missing character variable

PGStats — Wed, 03 Apr 2013 15:25:52 GMT

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

Re: Replacing missing character variable

Venki — Wed, 03 Apr 2013 15:38:48 GMT

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

Venki