topic Re: Need help with duplicates in long format data in SAS Programming

Need help with duplicates in long format data

hns — Wed, 05 Jun 2019 23:27:52 GMT

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 !

Re: Need help with duplicates in long format data

hns — Wed, 05 Jun 2019 23:35:37 GMT

PID Drug
1 A

1 B

1 A

1 C

2 B

2 C

Re: Need help with duplicates in long format data

ChrisNZ — Thu, 06 Jun 2019 00:12:19 GMT

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.

Re: Need help with duplicates in long format data

VDD — Thu, 06 Jun 2019 00:20:05 GMT

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/

Re: Need help with duplicates in long format data

Reeza — Thu, 06 Jun 2019 01:16:06 GMT

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.

Re: Need help with duplicates in long format data

hns — Thu, 06 Jun 2019 01:21:35 GMT

Thanks. It works now.