How to combine/transpose Medicaid claim data by ID and date?

Wolverine — Wed, 19 Nov 2014 16:41:51 GMT

I have Medicaid data, and there is a separate line for the category of each diagnosis (DX_cat) given at a visit to a doctor. I want to combine all of those diagnoses into a single line for each visit. Keep in mind that this requires combining with respect to ID (Encrpt_ID) AND date of service (ClaimDOS). I've found various methods to combine with respect to a single variable, but none for multiple variables. I'm guessing that SQL would be best for this, but I'm open to whatever works. I believe that the number of diagnoses that can be given at a single visit is limited to 9.

The input data looks like this:

Encrpt_ID	ClaimDOS	DX_cat
123	1-10-14	2
123	1-10-14	5
123	2-1-14	2
123	2-1-14	5
123	2-1-14	6
456	1-10-14	7
456	1-10-14	10
789	6-1-14	1

The output should look like this:

Encrpt_ID	ClaimDOS	DX_cat_1	DX_cat_2	DX_cat_3
123	1-10-14	2	5
123	2-1-14	2	5	6
456	1-10-14	7	10
789	6-1-14	1

Re: How to combine/transpose Medicaid claim data by ID and date?

stat_sas — Wed, 19 Nov 2014 16:59:32 GMT

proc transpose data=have out=want prefix=DX_cat_;

by Encrpt_ID ClaimDOS notsorted;

var DX_cat;

run;

Re: How to combine/transpose Medicaid claim data by ID and date?

Wolverine — Wed, 19 Nov 2014 19:19:33 GMT

That works nicely -- thank you! :smileygrin:

topic Re: How to combine/transpose Medicaid claim data by ID and date? in SAS Procedures

How to combine/transpose Medicaid claim data by ID and date?

Re: How to combine/transpose Medicaid claim data by ID and date?

Re: How to combine/transpose Medicaid claim data by ID and date?