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:
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.