change/set unknown to missing

[tan-wongv]

I have a data set with 3991 observations and 2 variables (DMRN and Sex). The value of sex was recorded as Female, Male, and Unknown. I tried to set "unknown" to " " by using an array but failed since the data set contain with both numeric and character variable. Can anyone help me with this issue?

Example of the data set:

DMRN        Sex

     1              Female

     2             Female

     3             Male

    4              Unknown

    5              Male

 3991           Female 

[whymath]

In the simplest situation, you don't really need array.

data have;
  input DMRN Sex $;
  cards;
1    Female
2    Female
3    Male
4    Unknown
5    Male
3991 Female
;
run;

data want;
  set have;
  if sex='Unknown' then sex='';
run;

If this does't help, please tell more details about your situation.

[tan-wongv]

Thank you for your response. I tried this before but unknown still remained. It did not change to ' '.

[tan-wongv]

This is my data set. only one sample with DMRN 3346 has unknown sex. I tried 
data want;

set sex;

if sex = 'unknown' then sex = ' ';

run;

But when I check the table it's still labeled as unknown.

[Patrick]

It's possible that your variable sex got a format attached to it and that you're trying to use the formatted values. Please run below code and share with us the result of the two procedures.

data test;
  set <your table>;
  sex2=sex;
run;

proc contents data=test(keep=sex);
run;
proc freq data=test;
  table sex2 /missing nocol norow nopercent;
run;

[tan-wongv]

Thank you for your help!

[SASKiwi · Accepted solution]

Text comparisons are case-sensitive. Your code needs to do an exact comparison:

data want;

set sex;

if sex = 'Unknown' then sex = ' ';
* or try this: if upcase(sex) = 'UNKNOWN' then sex = ' ';

run;

[tan-wongv]

Thank you so much! I tried the upcase and now it works!

[Kurt_Bremser]

Depending on your original data source, you might be able to handle this with a custom informat.