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
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;
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.
Thank you for your response. I tried this before but unknown still remained. It did not change to ' '.
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.
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;
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;
Thank you so much! I tried the upcase and now it works!
Depending on your original data source, you might be able to handle this with a custom informat.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.