Need help with duplicates in long format data

[hns]

Hi. 

I run into some trouble. 

I have the data in the following long format. 

PID 1 - Drug A

PID 1 - Drug B

PID 1 - Drug A

PID 1 - Drug C

PID 2 - Drug B

PID 2 - Drug C

PID 2 - Drug C

There are duplicates and I would like to transform it into wide format like this.

PID 1 = Drug A Drug B Drug C
PID 2 = Drug B Drug C

I tried to use nodupkeys but if I did that, PID 1 would only have Drug A and it would delete the other 2.

Thanks !

[hns]

PID    Drug
1          A

1          B

1          A

1          C

2          B

2          C

2          C

[ChrisNZ]

1. What are the variables in the before and after data?
  Provide as a data step please.

2. This change will probably make the data harder to use later on.

[VDD]

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

[Reeza · Accepted solution]

PROC SORT is correct, but you need to use both ID and DRUG in the BY statement so it removes duplicates along both. And then follow that with PROC TRANSPOSE.

[hns]

Thanks. It works now.