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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.