You'll need to explain how 1111/ d is part of lot 2 given that its claim date is different from b and c. What defines a lot? What define a treatment change? You'll need to explain the derivation of the lot2 end date. You'll need t explain why 2222 isn't finished ... View more

  I am not a good programmer.  This is very inelegant and not the best way.   I will assume the expected refill date is issue date plus days of supply.    Given: data scriptinfo; input MemberID issuedate:mmddyy10. NDC:$1. QuantityDispensed DaySupply; datalines; 1 1/1/2020 X 30 30 1 1/10/2020 Y 15 15 1 2/15/2020 X 30 30 1 3/19/2020 X 30 30 1 4/10/2020 Y 15 15 2 1/15/2020 X 180 180 2 3/18/2020 Y 30 30 2 4/15/2020 X 30 30 2 6/18/2020 Y 180 180 ; run;   We can sort the data by patient, drug, and date. Then calculate an expire date and a dayslate.   proc sort data = scriptinfo; by memberid ndc issuedate; run; data manipulate; set scriptinfo; by memberid ndc issuedate; expiredate = issuedate + daysupply; counter = first.ndc; *visual marker; dayslate = issuedate -lag(expiredate); if counter = 1 then dayslate = .; format issuedate date9. expiredate date9.; run; this gives us:   MemberID issuedate NDC QuantityDispensed DaySupply expiredate counter dayslate 1 1-Jan-20 X 30 30 31-Jan-20 1 . 1 15-Feb-20 X 30 30 16-Mar-20 0 15 1 19-Mar-20 X 30 30 18-Apr-20 0 3 1 10-Jan-20 Y 15 15 25-Jan-20 1 . 1 10-Apr-20 Y 15 15 25-Apr-20 0 76 2 15-Jan-20 X 180 180 13-Jul-20 1 . 2 15-Apr-20 X 30 30 15-May-20 0 -89 2 18-Mar-20 Y 30 30 17-Apr-20 1 . 2 18-Jun-20 Y 180 180 15-Dec-20 0 62   indicating patient 1 was 15 and then 3 days late when refilling his X while patient 2 was 89 days early refilling her 180 day supply of x.    You could probably average dayslate column by patient or by drug.     Maybe this will get you closer to where you need to be.   ... View more

Your description doesn't mention undergrad, grad, and nondegree. It's hard to understand what you have and what you want. With data like data have; input uid $6. intensity_flg; datalines; U13873 0 U24654 0 U24654 1 U24654 0 U25049 1 U45431 0 U45431 0 U64514 0 U66605 0 U66605 1 U66605 0 U70374 0 U70374 0 U70374 0 U81899 1 U82709 1 U82709 0 ; run; most people here would use the data step, sort, loop, and use first.  I'm an SQL guy so I use SQL. This: proc sql; select a.uid, a.intensity_flg, b.flag from have a left join (select uid, max(intensity_flg) as flag from have group by uid) b on a.uid = b.uid; quit; give this: uid intensity_flg flag U13873 0 0 U24654 0 1 U24654 1 1 U24654 0 1 U25049 1 1 U45431 0 0 U45431 0 0 U64514 0 0 U66605 0 1 U66605 1 1 U66605 0 1 U70374 0 0 U70374 0 0 U70374 0 0 U81899 1 1 U82709 0 1 U82709 1 1   ... View more

For something like that, I would collect data from anybody I could and not worry about sample sizes or anything.   The idea is identify the problem in the work flow, fix the problem, and make sure employees feel they have a voice in providing feedback.    Sounds like you just want an anonymous employee feedback mechanism.    ... View more

I think it is the Cartesian element (needing to compare everything to everything) that is the killer here.   There is probably a built in function that does this and I'm sure one of the SuperUser geniuses around here can tell you about it. I don't know it.    As a lessor mortal, I would take a no doubt incorrect line: 1. Convert your data to a list of groups with an ordered string that is the group members.  1 ABCD 2 ABC 3 DEF   2. Use a loop to compare 2 copies of the groups lists to each other using COMPGED. I am envisioning a matrix with the groups as row and column headings and a COMPGED score in each cell representing the diff in the strings for the row and column headings for that cell.    3.  If I knew how to write it, I would have done so.      Edit:   I still don't know how to make a list of strings representing the individuals in each group but my cartesian comment made me realize a simple join would do it.    So: data orig; input GroupID Indstring $10.; datalines; 1 ABCD 2 ABC 3 DEF ; run; proc sql; select a.indstring, b.indstring, compged(a.indstring, b.indstring) from orig a, orig b; run; gives me    Indstring Indstring   ABCD ABCD 0 ABCD ABC 50 ABCD DEF 450 ABC ABCD 10 ABC ABC 0 ABC DEF 400 DEF ABCD 410 DEF ABC 400 DEF DEF 0   Low score is high overlap, high score is low overlap.  Bring along other data elements as needed. Name things better.  Delete the rows where the strings are the same.  Sort in asc or desc order. Scale the score to whatever flag setting you like.      Edit #2: A Cartesian match with 4.8 million groups?  I'm out. ... View more

Here is one option that works with this particular data set:   data orig; input rowN contractNo repeatNo contract_repeat cate:$10. ctype:$10. ccnt L_tot_price P_tot_price; datalines; 1 1555012 7 15550127 partial cancel -1 -700 -200 2 1555012 7 15550127 partial new 1 2000 100 3 1555017 8 15550178 partial new 1 9620 93 4 1555018 12 155501812 partial new 1 0 0 5 1555021 6 15550216 partial new 1 23430 455 6 1555021 6 15550216 partial cancel -1 -22230 -405 7 1555021 6 15550216 partial new 1 22230 405 8 1555021 6 15550216 partial cancel -1 -23430 -455 9 1555021 6 15550216 partial new 1 14210 296 ; run; data working; set orig; absccnt = abs(ccnt); absL_tot_price = abs(L_tot_price); absP_tot_price = abs(P_tot_price); run; proc sql; select * from working group by absccnt, absL_tot_price, absP_tot_price having count(*) < 2 order by rowN; quit; Output data set is: rowN contractNo repeatNo contract_repeat cate ctype ccnt L_tot_price P_tot_price absccnt absL_tot_price absP_tot_price 1 1555012 7 15550127 partial cancel -1 -700 -200 1 700 200 2 1555012 7 15550127 partial new 1 2000 100 1 2000 100 3 1555017 8 15550178 partial new 1 9620 93 1 9620 93 4 1555018 12 1.56E+08 partial new 1 0 0 1 0 0 9 1555021 6 15550216 partial new 1 14210 296 1 14210 296     ... View more

Perhaps create the table in SAS and then export when you are done. ... View more