Merging patient data with multiple claims and diagnostic/procedure codes

[cmolina989]

I was given the following scenario. I know it involves a PROC TRANPOSE (or multiple) to get it from long data to wide data, but couldn't get it to work.

File1 contains patient identifying number, first name, last name, social security number, date of birth, and sex on 100,000 persons, with one record per patient.

File2 contains medical claim information (n=300,000 records) on many but not all of the patients in File1. Each record in File2 includes the patient identifying number, claim date, claim number, up to two diagnosis codes, and up to two procedure codes, with potentially multiple claims per patient. The number of claims per patient is not the same for every patient.

You are asked to create File3 from File1 and File2.

Link the data in file1 to file2 on patient identifying number, producing file3 that contains one record per patient including patient identifying number, name, DOB, the first claim date, and all diagnosis and procedure codes for all claims for that person.

[CarmineVerrell]

Sounds like you can get your answer by joining /merging data. This would be easier for us if you provide some made up data and example of desired result. 

[Reeza]

File 1 - do nothing

File 2 - transpose so that you have one record per person with the detailed information required. See the long to wide transpose tutorials below, a data step is faster and more efficient, but for a beginner programmer the multiple transpose and join is likely easier to program and more intuitive. Plus it's fully dynamic for your data.

Match Merge the data after you've transposed them. 

http://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lepg/p1phdzzlrc1wi8n1bpigstwt851o.htm

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

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

---

@cmolina989 wrote:

I was given the following scenario. I know it involves a PROC TRANPOSE (or multiple) to get it from long data to wide data, but couldn't get it to work.

File1 contains patient identifying number, first name, last name, social security number, date of birth, and sex on 100,000 persons, with one record per patient.

File2 contains medical claim information (n=300,000 records) on many but not all of the patients in File1. Each record in File2 includes the patient identifying number, claim date, claim number, up to two diagnosis codes, and up to two procedure codes, with potentially multiple claims per patient. The number of claims per patient is not the same for every patient.

You are asked to create File3 from File1 and File2.

Link the data in file1 to file2 on patient identifying number, producing file3 that contains one record per patient including patient identifying number, name, DOB, the first claim date, and all diagnosis and procedure codes for all claims for that person.

---

[Tom]

Unless you are planning to do some machine learning you probably do not want building some extremely wide dataset without any structure.

What is your research goal?

Are you looking to find diagnostics codes that occur after a procedure? Perhaps to look for adverse events from the procedure?

Are you looking to find procedure codes that occur after a diagnosis? Perhaps to looks for treatments for a disease?