Hi @righcoastmike Thank your for your patience and feedback. Can you post a more comprehensive sample of your HAVE and the WANT. I have a feeling, I am failing to understand the requirement somewhere.
Objective : To find study id's have 3 or more ed_visits within 12 months ( we'll call them repeat users)
My understanding: If interval between "any 2 dates is <=12", that qualifies. So if month interval between first record of studyid and 3rd record or nth record of studyid is <=12, that still qualifies.
So once we have the dates extracted
md=input(compress(mth_date,'-'),yymmn6.);
Here is how the HASH logic works:
1. Dataset temp with extracted date is sorted by studyid for By group processing
2. For every observation of By group, the look up happens for all obs for it's equivalent. So if studyid has 3 obs as in the sample, the look up happens 3*3=9 times i.e 1 obs of studyid1 with all 3 obs of studyid 1, then 2nd obs of studyid1 with all 3 obs of studyid 1 etc.
3. Since it is a look ahead(next set of records i.e future dates) the look up(hash table) md variable should be greater than the current md variable of the table from which the look up is taking place.
4. Should 3 be true, we check whether number of months between the two dates is <=12. If 3 and 4 happen to be true, we sum ed_visit of current +ed_visits of the future(look up table)
4. The sum is a running total across each by group
5. At the end of each by group should the cumulative sum is >=3 then that entire group should be flagged as 1 else 0
6. In instances, where there is only one record for a by group, should the value of ed_visits is >=3 that should also be flagged as 1.
If the above is correct, I think where i perhaps was an idiot is likely this part that I am noticing now, that misses it is
The logic requires change to-->
do rc=h.find() by 0 while(rc=0);
if md>_md and intck('month',_md,md)<=12 then do;
k=sum(ed_visits,_ed_visits);
_k+k;
end;
rc=h.find_next();
end;
from the earlier below
do rc=h.find() by 0 while(rc=0);
if md>_md and intck('month',_md,md)<=12 then k=sum(ed_visits,_ed_visits);
rc=h.find_next();
end;
_k+k;
So the changed full version is
data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H (dataset:'have',ordered: "A",multidata:'y') ;
h.definekey ("studyID") ;
h.definedata ("md","ed_visits") ;
h.definedone () ;
end;
_k=0;
do until(last.studyid);
set have(rename=(md=_md ed_visits=_ed_visits));
by studyID;
do rc=h.find() by 0 while(rc=0);
if md>_md and intck('month',_md,md)<=12 then do;
k=sum(ed_visits,_ed_visits);
_k+k;
end;
rc=h.find_next();
end;
end;
repeat_user=first.studyid and last.studyid and _ed_visits>=3 or k>=3 ;
do until(last.studyid);
set have;
by studyID;
output;
end;
keep studyid mth_date ed_visits year repeat_user;
run;
Let me know. Sorry and Thank you for your patience.
