topic Re: Combining rows with duplicate id's into one row with condition in SAS Programming

Combining rows with duplicate id's into one row with condition

nharuka — Tue, 28 Jun 2022 21:11:06 GMT

I have a dataset with multiple observations per person. I want to combine the ones with the same id into one row but I want to keep descending values. How can I do this?

Current Data

ID     RANDFL  COMPFL  TIMEFL
001       Y      Y       N 
001       N      N       Y
001       N      N       Y
002       N      Y       Y
002       N      N       N
002       N      N       N
003       Y      N       N
003       Y      Y       N
003       Y      N       N

Wanted Data

ID     RANDFL  COMPFL  TIMEFL
001       Y       Y      Y   
002       N       Y      Y
003       Y       Y      N

Re: Combining rows with duplicate id's into one row with condition

Reeza — Tue, 28 Jun 2022 21:16:47 GMT

You can use SQL and the fact that you can aggregate on character columns.

If you want Y to override N, then use the Max function.

proc sql;
create table want as
select ID, max(randfl) as randfl, max(compfl) as compfl, max(timefl) as timefl
from have
group by ID
order by ID;
quit;

@nharuka wrote:

I have a dataset with multiple observations per person. I want to combine the ones with the same id into one row but I want to keep descending values. How can I do this?

Current Data
ID     RANDFL  COMPFL  TIMEFL
001       Y      Y       N 
001       N      N       Y
001       N      N       Y
002       N      Y       Y
002       N      N       N
002       N      N       N
003       Y      N       N
003       Y      Y       N
003       Y      N       N
Wanted Data
ID     RANDFL  COMPFL  TIMEFL
001       Y       Y      Y   
002       N       Y      Y
003       Y       Y      N

Re: Combining rows with duplicate id's into one row with condition

nharuka — Tue, 28 Jun 2022 21:25:25 GMT

It worked. Thank you!