topic Re: Creating one variable per multiple rows per ID in SAS Programming

Creating one variable per multiple rows per ID

nataliavanessa0 — Fri, 03 Sep 2021 12:57:23 GMT

Hello ,

I am trying to create one variable (type2) from multiple rows per ID using variable type. I want to covert a table like this one:

ID	type
1	A
1	A
1	B
2	A
2	A
3	B
3	B
3	B

To:

ID	type2
1	C
2	A
3	B

Where type2 is:

If type is always A then type2=A;

If type is always B then type2=B;

If type is a combination of A and B then type2=C;

Thanks

Re: Creating one variable per multiple rows per ID

Ksharp — Fri, 03 Sep 2021 14:00:36 GMT

data have;
input ID
type $;
cards;
1
A
1
A
1
B
2
A
2
A
3
B
3
B
3
B
;
proc sql;
create table want as
select id,case 
when sum(type='A')=count(type) then 'A' 
when sum(type='B')=count(type) then 'B'
when sum(type='A') ne 0 and sum(type='B') ne 0 then 'C'
else ' ' end as type2
 from have 
  group by id;
quit;