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

[nharuka]

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

[Reeza · Accepted solution]

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

---

[nharuka]

It worked. Thank you!