Hello,
I'm trying to create observational groups based on the types of post operative pain medications that a patient received. I have 5 dummy variables coded 1/0, and have been trying to use those 5 variables to account for all combinations and create a variable that contains each combo.
However something isn't quite working right. Hopefully it's something simple with the way that I'm using IF/THEN statements.
To account for all combinations of my dummy variables I did a multidimensional contingency table.
proc freq data = ty_analysis;
table pre_op*DOS_op*post_op pre_ty*DOS_ty*post_ty pre_keto*DOS_keto*post_keto pre_ibu*DOS_ibu*post_ibu pre_nar*DOS_nar*post_nar /list;
run;
post_op | post_IV | post_ty | post_keto | post_ibu | Frequency | Percent | Cumulative | Cumulative |
0 | 0 | 0 | 0 | 0 | 23711 | 20.16 | 23711 | 20.16 |
0 | 0 | 0 | 0 | 1 | 1281 | 1.09 | 24992 | 21.25 |
0 | 0 | 0 | 1 | 0 | 10378 | 8.82 | 35370 | 30.08 |
0 | 0 | 0 | 1 | 1 | 592 | 0.50 | 35962 | 30.58 |
0 | 0 | 1 | 0 | 0 | 3497 | 2.97 | 39459 | 33.55 |
0 | 0 | 1 | 0 | 1 | 1737 | 1.48 | 41196 | 35.03 |
0 | 0 | 1 | 1 | 0 | 5998 | 5.10 | 47194 | 40.13 |
0 | 0 | 1 | 1 | 1 | 863 | 0.73 | 48057 | 40.86 |
0 | 1 | 1 | 0 | 0 | 243 | 0.21 | 48300 | 41.07 |
0 | 1 | 1 | 0 | 1 | 96 | 0.08 | 48396 | 41.15 |
0 | 1 | 1 | 1 | 0 | 987 | 0.84 | 49383 | 41.99 |
0 | 1 | 1 | 1 | 1 | 248 | 0.21 | 49631 | 42.20 |
1 | 0 | 0 | 0 | 0 | 18730 | 15.93 | 68361 | 58.13 |
1 | 0 | 0 | 0 | 1 | 2854 | 2.43 | 71215 | 60.55 |
1 | 0 | 0 | 1 | 0 | 17927 | 15.24 | 89142 | 75.80 |
1 | 0 | 0 | 1 | 1 | 1606 | 1.37 | 90748 | 77.16 |
1 | 0 | 1 | 0 | 0 | 6300 | 5.36 | 97048 | 82.52 |
1 | 0 | 1 | 0 | 1 | 2347 | 2.00 | 99395 | 84.52 |
1 | 0 | 1 | 1 | 0 | 9295 | 7.90 | 108690 | 92.42 |
1 | 0 | 1 | 1 | 1 | 2940 | 2.50 | 111630 | 94.92 |
1 | 1 | 1 | 0 | 0 | 1130 | 0.96 | 112760 | 95.88 |
1 | 1 | 1 | 0 | 1 | 430 | 0.37 | 113190 | 96.25 |
1 | 1 | 1 | 1 | 0 | 3034 | 2.58 | 116224 | 98.83 |
1 | 1 | 1 | 1 | 1 | 1381 | 1.17 | 117605 | 100.00 |
Using the the table to account for all groups I coded my postgroup variable
if post_op EQ 1 & post_IV EQ 1 & post_ty EQ 1 & post_keto EQ 1 & post_ibu EQ 1 then postgroup = " OTAKI ";
else if post_op EQ 1 & post_IV EQ 1 & post_ty EQ 1 & post_keto EQ 1 & post_ibu EQ 0 then postgroup = " OTAK ";
else if post_op EQ 1 & post_IV EQ 1 & post_ty EQ 1 & post_keto EQ 0 & post_ibu EQ 1 then postgroup = " OTAI ";
else if post_op EQ 1 & post_IV EQ 1 & post_ty EQ 1 & post_keto EQ 0 & post_ibu EQ 0 then postgroup = " OTA ";
else if post_op EQ 1 & post_IV EQ 0 & post_ty EQ 1 & post_keto EQ 1 & post_ibu EQ 1 then postgroup = " OAKI ";
else if post_op EQ 1 & post_IV EQ 0 & post_ty EQ 1 & post_keto EQ 1 & post_ibu EQ 0 then postgroup = " OAK ";
else if post_op EQ 1 & post_IV EQ 0 & post_ty EQ 1 & post_keto EQ 0 & post_ibu EQ 1 then postgroup = " OAI ";
else if post_op EQ 1 & post_IV EQ 0 & post_ty EQ 1 & post_keto EQ 0 & post_ibu EQ 0 then postgroup = " OA ";
else if post_op EQ 1 & post_IV EQ 0 & post_ty EQ 0 & post_keto EQ 1 & post_ibu EQ 1 then postgroup = " OKI ";
else if post_op EQ 1 & post_IV EQ 0 & post_ty EQ 0 & post_keto EQ 1 & post_ibu EQ 0 then postgroup = " OK ";
else if post_op EQ 1 & post_IV EQ 0 & post_ty EQ 0 & post_keto EQ 0 & post_ibu EQ 1 then postgroup = " OI ";
else if post_op EQ 1 & post_IV EQ 0 & post_ty EQ 0 & post_keto EQ 0 & post_ibu EQ 0 then postgroup = " O ";
else if post_op EQ 0 & post_IV EQ 1 & post_ty EQ 1 & post_keto EQ 1 & post_ibu EQ 1 then postgroup = " TAKI ";
else if post_op EQ 0 & post_IV EQ 1 & post_ty EQ 1 & post_keto EQ 1 & post_ibu EQ 0 then postgroup = " TAK ";
else if post_op EQ 0 & post_IV EQ 1 & post_ty EQ 1 & post_keto EQ 0 & post_ibu EQ 1 then postgroup = " TAI ";
else if post_op EQ 0 & post_IV EQ 1 & post_ty EQ 1 & post_keto EQ 0 & post_ibu EQ 0 then postgroup = " TA ";
else if post_op EQ 0 & post_IV EQ 0 & post_ty EQ 1 & post_keto EQ 1 & post_ibu EQ 1 then postgroup = " AKI ";
else if post_op EQ 0 & post_IV EQ 0 & post_ty EQ 1 & post_keto EQ 1 & post_ibu EQ 0 then postgroup = " AK ";
else if post_op EQ 0 & post_IV EQ 0 & post_ty EQ 1 & post_keto EQ 0 & post_ibu EQ 1 then postgroup = " AI ";
else if post_op EQ 0 & post_IV EQ 0 & post_ty EQ 1 & post_keto EQ 0 & post_ibu EQ 0 then postgroup = " A ";
else if post_op EQ 0 & post_IV EQ 0 & post_ty EQ 0 & post_keto EQ 1 & post_ibu EQ 1 then postgroup = " KI ";
else if post_op EQ 0 & post_IV EQ 0 & post_ty EQ 0 & post_keto EQ 1 & post_ibu EQ 0 then postgroup = " K ";
else if post_op EQ 0 & post_IV EQ 0 & post_ty EQ 0 & post_keto EQ 0 & post_ibu EQ 1 then postgroup = " I ";
else if post_op EQ 0 & post_IV EQ 0 & post_ty EQ 0 & post_keto EQ 0 & post_ibu EQ 0 then postgroup = " NONE ";
The problem is that running a proc freq on postgroup, the counts for each grouping don't add up and I have some obs for instance that are 0 in all dummy variables being added to different groups instead of the NONE category.
postgroup | Frequency | Percent | Cumulative | Cumulative |
NONE | 22547 | 19.17 | 22547 | 19.17 |
O | 19020 | 16.17 | 41567 | 35.34 |
OK | 18380 | 15.63 | 59947 | 50.97 |
K | 10513 | 8.94 | 70460 | 59.91 |
OAK | 9343 | 7.94 | 79803 | 67.86 |
OA | 6327 | 5.38 | 86130 | 73.24 |
AK | 6018 | 5.12 | 92148 | 78.35 |
A | 3510 | 2.98 | 95658 | 81.34 |
OTAK | 3034 | 2.58 | 98692 | 83.92 |
OAKI | 2952 | 2.51 | 101644 | 86.43 |
OI | 2939 | 2.50 | 104583 | 88.93 |
OAI | 2360 | 2.01 | 106943 | 90.93 |
AI | 1750 | 1.49 | 108693 | 92.42 |
OKI | 1633 | 1.39 | 110326 | 93.81 |
OTAKI | 1381 | 1.17 | 111707 | 94.98 |
I | 1301 | 1.11 | 113008 | 96.09 |
OTA | 1131 | 0.96 | 114139 | 97.05 |
TAK | 987 | 0.84 | 115126 | 97.89 |
AKI | 868 | 0.74 | 115994 | 98.63 |
KI | 594 | 0.51 | 116588 | 99.14 |
OTAI | 430 | 0.37 | 117018 | 99.50 |
TAKI | 248 | 0.21 | 117266 | 99.71 |
TA | 243 | 0.21 | 117509 | 99.92 |
TAI | 96 | 0.08 | 117605 | 100.00 |
post_op | post_IV | post_ty | post_keto | post_ibu | postgroup | Frequency | Percent | Cumulative | Cumulative |
0 | 0 | 0 | 0 | 0 | KI | 2 | 0.34 | 2 | 0.34 |
0 | 0 | 0 | 1 | 1 | KI | 592 | 99.66 | 594 | 100.00 |
Without your data and complete code it is almost impossible to determine why as we would need examples that create the incorrect assignment.
I am going to suggest an alternate form of coding the postgroup that might help as that many if then else statements have lots of potential for headaches.
proc format library=work; value O 1='O' other=' '; value T 1='T' other=' '; value A 1='A' other=' '; value K 1='K' other=' '; value I 1='I' other=' '; RUN; data example; input post_op post_iv post_ty post_keto post_ibu; length postgroup $ 5. ; postgroup= cats(put(post_op,o.),put(post_iv, t.),put(post_ty, a.),put(post_keto, k.),put(post_ibu, i.) ); if missing(postgroup) then postgroup='NONE'; datalines; 0 0 0 0 0 0 1 0 0 1 1 1 1 1 1 1 0 1 0 1 ; run;
I would suggest that the most likely issue relates to how you get the proc freq output into a single data set as your example "data" does not match the output that would be created by the proc freq code you show.
Sorry, completely forgot to attach a paired down dataset. I've updated the post.
Thanks for the response, and suggested code. Still learning my way around SAS and my first attempt is definitely clunky. I'll give this approach a try.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: