How do I transpose the data below to a single observation:
Member ID | Fill Date | index date | days_supply |
603 | 2/17/2005 | 2/17/2005 | 30 |
603 | 6/13/2005 | 2/17/2005 | 30 |
603 | 8/11/2005 | 2/17/2005 | 30 |
End goal is for the data above to look like this:
Member ID | Fill date 1 | Fill date 2 | Fill Date 3 | days supply 1 | days supply 2 | days supply 3 | Start Date | End Date |
603 | 2/17/2005 | 6/13/2005 | 8/11/2005 | 30 | 30 | 30 | 2/17/2005 | 8/15/2005 |
Thanks!
Solved! Go to Solution.
Hi I've put together a sample workflow for you:
Essentially I use some REGEX_Replace to turn each of the fields into a number, and check the value 3 rows ahead (which is the same field, for the previous record) to see what the number should be. If there is no number, it becomes a 1, otherwise you add one.
I then filter out unwanted fields, compute the maximum and minimum fill dates, CrossTab the data back into a horizontal format, and join in the start and end dates on Member ID.
To do this with additional members, just remember to group by MemberID in the Transpose, CrossTab, and MultiRow Formula Tools.
Let me know if this works for your use-case\
Cheers!
Thank you for your help.
Follow-up Question: how do I take the last data set:
Member ID | Fill date 1 | Fill dAte 2 | Fill Date 3 | days supply 1 | days supply 2 | days supply 3 | Start Date | End Date |
603 | 2/17/2005 | 6/13/2005 | 8/11/2005 | 30 | 30 | 30 | 2/17/2005 | 8/15/2005 |
and transpose: Please see below: This will require me using loops and arrays to find the days of medication coverage for each member and calculates the proportion of covered days in the review period. In this case the covered proportion of days covered is 65 over 180 days or .361
Member ID | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | Day 6 | Day 7 | Day 8 | Day 9 | Day 10 | Day 11 | Day 12 | Day 13 | Day 14 | Day 15 | Day 16 | Day 17 | Day 18 | Day 19 | Day 20 | Day 21 | Day 22 | Day 23 | Day 24 | Day 25 | Day 26 | Day 27 | Day 28 | Day 29 | Day 30 |
603 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Day 31 | Day 32 | Day 33 | Day 34 | Day 35 | Day 36 | Day 37 | Day 38 | Day 39 | Day 40 | Day 41 | Day 42 | Day 43 | Day 44 | Day 45 | Day 46 | Day 47 | Day 48 | Day 49 | Day 50 | Day 51 | Day 52 | Day 53 | Day 54 | Day 55 | Day 56 | Day 57 | Day 58 | Day 59 | Day 60 | |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Day 61 | Day 62 | Day 63 | Day 64 | Day 65 | Day 66 | Day 67 | Day 68 | Day 69 | Day 70 | Day 71 | Day 72 | Day 73 | Day 74 | Day 75 | Day 76 | Day 77 | Day 78 | Day 79 | Day 80 | Day 81 | Day 82 | Day 83 | Day 84 | Day 85 | Day 86 | Day 87 | Day 88 | Day 89 | Day 90 | |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
Day 91 | Day 92 | Day 93 | Day 94 | Day 95 | Day 96 | Day 97 | Day 98 | Day 99 | Day 100 | Day 101 | Day 102 | Day 103 | Day 104 | Day 105 | Day 106 | Day 107 | Day 108 | Day 109 | Day 110 | Day 111 | Day 112 | Day 113 | Day 114 | Day 115 | Day 116 | Day 117 | Day 118 | Day 119 | Day 120 | |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | |
Day 121 | Day 122 | Day 123 | Day 124 | Day 125 | Day 126 | Day 127 | Day 128 | Day 129 | Day 130 | Day 131 | Day 132 | Day 133 | Day 134 | Day 135 | Day 136 | Day 137 | Day 138 | Day 139 | Day 140 | Day 141 | Day 142 | Day 143 | Day 144 | Day 145 | Day 146 | Day 147 | Day 148 | Day 149 | Day 150 | |
1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | |
Day 151 | Day 152 | Day 153 | Day 154 | Day 155 | Day 156 | Day 157 | Day 158 | Day 159 | Day 160 | Day 161 | Day 162 | Day 163 | Day 164 | Day 165 | Day 166 | Day 167 | Day 168 | Day 169 | Day 170 | Day 171 | Day 172 | Day 173 | Day 174 | Day 175 | Day 176 | Day 177 | Day 178 | Day 179 | Day 180 | |
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | |
Days Covered | p_days_covered | |||||||||||||||||||||||||||||
65 | 0.36111 |