Dear,
From my data I am able to get output I need by several steps. Please suggest any better way to do. Thank you
data one;
input id white $ asian $ africanamerican $ americanindian $ hawaiian $;
datalines;
1 0 0 0 0 1
2 1 0 0 0 0
3 1 0 0 0 1
4 1 0 0 1 1
;
proc transpose data=one out=two(where=(col1='1'));
by id;
var white asian africanamerican americanindian hawaiian;
run;
proc freq data=two;
tables id/out=three(drop=percent)noprint;
run;
data four;
merge one(in=a) two three;
by id;
if a;
if count=1 then race=_name_;
else if count gt 1 then race='multiple';
run;
proc transpose data=four(where=(count gt 1)) out=five(drop=_name_ _label_) prefix=race;
by id;
var _name_;
run;
data six;
merge four(in=a) five;
by id;
keep id white asian africanamerican americanindian hawaiian race race1 race2 race3;
run;
/*output needed*/
white asian africanamerican americanindian hawaiian race race1 race2 race3
0 0 0 0 1 Hawaiian
1 0 0 0 0 white
1 0 0 0 1 multiple white hawaiian
1 0 0 1 1 multiple white american indian hawaiian
Does this extend beyond the three races? Otherwise a SELECT or IF/THEN seems simpler.
Yes. In my actual data some subjects have 4 races. Thank you
Yeah, that's not clear. Is this example fully representative of your actual data and what you need? If so, try rewriting it as IF/Then statements.
Please try the below code to see the expected output
data one;
input id white $ asian $ africanamerican $ americanindian $ hawaiian $;
datalines;
1 0 0 0 0 1
2 1 0 0 0 0
3 1 0 0 0 1
4 1 0 0 1 1
;
data want;
set one;
array cd(*) white asian africanamerican americanindian hawaiian;
array rac(*) $20. race1-race5;
do i = 1 to 5;
if cd(i) eq 1 then rac(i)=vname(cd(i));
end;
count=sum(white, asian, africanamerican, americanindian ,hawaiian);
if count=1 then race=coalescec(of race1-race5);
else if count>1 then race='Multiple';
run;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.