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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.