DATA Step, Macro, Functions and more

How to get output of an variable based on values present 5 variables

Reply
Super Contributor
Posts: 270

How to get output of an variable based on values present 5 variables

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

Super User
Posts: 17,731

Re: How to get output of an variable based on values present 5 variables

Does this extend beyond the three races? Otherwise a SELECT or IF/THEN seems simpler. 

Super Contributor
Posts: 270

Re: How to get output of an variable based on values present 5 variables

Yes. In my actual data some subjects have 4 races. Thank you

Super User
Posts: 17,731

Re: How to get output of an variable based on values present 5 variables

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. 

Trusted Advisor
Posts: 1,128

Re: How to get output of an variable based on values present 5 variables

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;

Thanks,
Jag
Ask a Question
Discussion stats
  • 4 replies
  • 139 views
  • 1 like
  • 3 in conversation