topic Re: change/set unknown to missing in New SAS User

change/set unknown to missing

tan-wongv — Wed, 13 Mar 2024 03:47:04 GMT

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

Re: change/set unknown to missing

whymath — Wed, 13 Mar 2024 04:05:09 GMT

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.

Re: change/set unknown to missing

tan-wongv — Wed, 13 Mar 2024 04:11:51 GMT

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

Re: change/set unknown to missing

tan-wongv — Wed, 13 Mar 2024 04:18:29 GMT

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.

Re: change/set unknown to missing

Patrick — Wed, 13 Mar 2024 04:25:58 GMT

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;

Re: change/set unknown to missing

SASKiwi — Wed, 13 Mar 2024 04:23:53 GMT

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;

Re: change/set unknown to missing

Kurt_Bremser — Wed, 13 Mar 2024 07:04:58 GMT

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

Re: change/set unknown to missing

tan-wongv — Wed, 13 Mar 2024 20:33:43 GMT

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

Re: change/set unknown to missing

tan-wongv — Wed, 13 Mar 2024 20:34:19 GMT

Thank you for your help!