Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

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

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_IDClaimDOSDX_cat
123

1-10-14

2
1231-10-145
1232-1-142
1232-1-145
1232-1-146
4561-10-147
4561-10-1410
7896-1-141

The output should look like this:

Encrpt_IDClaimDOSDX_cat_1DX_cat_2DX_cat_3
1231-10-1425
1232-1-14256
4561-10-14710
7896-1-141

Accepted Solutions
Solution
‎11-19-2014 11:59 AM
Trusted Advisor
Posts: 1,228

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

Posted in reply to Wolverine

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

  by Encrpt_ID ClaimDOS notsorted;

  var DX_cat;

run;

View solution in original post


All Replies
Solution
‎11-19-2014 11:59 AM
Trusted Advisor
Posts: 1,228

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

Posted in reply to Wolverine

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

  by Encrpt_ID ClaimDOS notsorted;

  var DX_cat;

run;

Contributor
Posts: 42

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

That works nicely -- thank you!  :smileygrin:

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 185 views
  • 0 likes
  • 2 in conversation