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

[Wolverine]

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

[stat_sas · Accepted solution]

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

  by Encrpt_ID ClaimDOS notsorted;

  var DX_cat;

run;

[Wolverine]

That works nicely -- thank you!  :smileygrin: