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 |
proc transpose data=have out=want prefix=DX_cat_;
by Encrpt_ID ClaimDOS notsorted;
var DX_cat;
run;
proc transpose data=have out=want prefix=DX_cat_;
by Encrpt_ID ClaimDOS notsorted;
var DX_cat;
run;
That works nicely -- thank you! :smileygrin:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.