DATA Step, Macro, Functions and more

Create seq ID, retain values until an event happens

Reply
Frequent Contributor
Posts: 107

Create seq ID, retain values until an event happens

Sorry. I accidentally deleted my post. Had to repost again.

 

I've a data set listing an ID's visits to each facility (fac_code) with start date and endate date and visit type, and the facility woud assign an EPID (episode ID) like chart number to the persono, but the faciltiy might give people a different EP ID upont their return.  So now I'd like to bundle such cases, by creating a fac seq key and an meta episode ID so that 'm able to group them together for my analysis later. For a simpler scenario (#1) I had the code written, as below. But there is another layer or complexity (#2) that I'm not able to resolve and hope you guys have a solution (ideally just expanding the existent code.)

1. if the person returned to the home consecutively (e.g. ID 3, A > A), then I'd like to assign a facility sequal ID (starting with 1) to buncle them together and if the person has differnet EPIDs for the same facility (if returning to the same one) , I'd retain the first EPID (e.g. ID3 to facilty A). But if the person goes from facilty A, B and then A, the sequence would be 1,2,3, not 1,2,1 (ID 4).
I've the code as below. The output values for fac_seq and EPID2 are the same as the second set of columns from the right in my data input (out1_facseq out1_epID2) .

2. But I now need to take into account of the visit type (vst_type). Basically if the first visits are SH (short, before it becomes longstay), I need to assign fac_seq as 0 (eg ID 2) and EPID stay unchanged (so EPID2=EPID) until there is a LS visit, then fac_Seq becomes 1 and EPID follows the same rules as #1 above. If SH takes place after LS, then it follows the same rule as LS (e.g. ID=4). The last set of columns in the data input (out2_facseq out2_epID2) are my "want" values.

I tried to revise my code in step 1 with the following changes, but it didn't assign ID 3 (record 3) and ID 4 (record 4) right. (Again it's best the solution is just expanding the existnet code, not entirely new code.).

 

   from
       by ID fac_code notsorted;
       if first.ID then fac_seq=0;
       if first.fac_code then fac_seq+1;
  to     

       by ID fac_code notsorted;
       if first.ID then fac_seq=0;
       if first.fac_code and vst_type in ("LS") then fac_seq+1;

data have; input ID 1  fac_code $ 3 epID 6-8 entry_dt 10-13 disch_dt 15-19 vst_type $ 21-22 out1_facseq 27 out1_epID2  30-32 out2_facseq 36 out2_epID2 39-41;
datalines;
1 A  110 2011 2012  LS    1  110   1  110
1 A  110 2012 2014  LS    1  110   1  110
1 A  110 2014 2015  LS    1  110   1  110
1 B  120 2015 2016  LS    2  120   2  120
2 A  300 2011 2013  SH    1  300   0  300
2 A  305 2013 2013  SH    1  300   0  305
2 B  410 2014 2014  LS    2  410   1  410
3 A  220 2011 2013  SH    1  220   0  220
3 A  220 2013 2013  SH    1  220   0  220
3 A  250 2014 2015  LS    1  220   1  250
3 B  300 2015 2016  LS    2  300   2  300
4 A  505 2011 2013  SH    1  505   0  505
4 B  620 2013 2013  LS    2  620   1  620
4 B  620 2014 2015  LS    2  620   1  620
4 A  505 2015 2016  SH    3  505   2  505
;
proc print; run;

*step 1 - assign fac seq;
proc sort data=have out=have;     by ID entry_dt disch_dt; run;
data have; format ID fac_code fac_seq;
    set have;
    by ID fac_code notsorted;
    if first.ID then fac_seq=0;
    if first.fac_code then fac_seq+1;
run;
proc print; run;

*2 -create EPID2;
proc sort data=have; by ID fac_code fac_seq entry_dt; run;
data want; format ID fac_code fac_seq epID epID2;
    set have;
    by ID fac_code fac_seq entry_dt;
    retain epID2;
    if first.fac_seq then epID2=epID;
run;
proc sort data=want; by ID entry_dt; run;
proc print; run;

PROC Star
Posts: 7,474

Re: Create seq ID, retain values until an event happens

I don't quite understand your rules, but the following matches your 'want' with the exception of one epid2 assignment:

data have;
   input ID 1  fac_code $ 3 epID 6-8 entry_dt 10-13 disch_dt 15-19 vst_type $ 21-22
         out1_facseq 27 out1_epID2  30-32 out2_facseq 36 out2_epID2 39-41;
   datalines;
1 A  110 2011 2012  LS    1  110   1  110
1 A  110 2012 2014  LS    1  110   1  110
1 A  110 2014 2015  LS    1  110   1  110
1 B  120 2015 2016  LS    2  120   2  120
2 A  300 2011 2013  SH    1  300   0  300
2 A  305 2013 2013  SH    1  300   0  305
2 B  410 2014 2014  LS    2  410   1  410
3 A  220 2011 2013  SH    1  220   0  220
3 A  220 2013 2013  SH    1  220   0  220
3 A  250 2014 2015  LS    1  220   1  250
3 B  300 2015 2016  LS    2  300   2  300
4 A  505 2011 2013  SH    1  505   0  505
4 B  620 2013 2013  LS    2  620   1  620
4 B  620 2014 2015  LS    2  620   1  620
4 A  505 2015 2016  SH    3  505   2  505
;

*step 1 - assign fac seq;
proc sort data=have out=have;
     by ID entry_dt disch_dt;
run;
data have (drop=last_vst_type);
    format ID fac_code fac_seq;
    set have;
    by ID fac_code notsorted;
    retain fac_seq;
    if first.ID then fac_seq=0;
    last_vst_type=lag(vst_type);
    fac_seq=ifn((lag(fac_code) ne fac_code and not first.id) or
          (first.fac_code and vst_type in ("LS") or
          (not first.id and vst_type in ("LS") and vst_type ne last_vst_type)),
          fac_seq+1,fac_seq);
run;

*2 -create EPID2;
proc sort data=have;
 by ID fac_code fac_seq entry_dt;
run;

data want;
    format ID fac_code fac_seq epID epID2;
    set have;
    by ID fac_code fac_seq entry_dt;
    retain epID2;
    epID2=ifn(lag(fac_code) ne fac_code or first.fac_seq,epID,epID2);
run;

proc sort data=want;
 by ID entry_dt;
run;

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 107

Re: Create seq ID, retain values until an event happens

Thanks Art. It worked, except for one scenario. ID2, row 2. The person EPID is 300 in the first row (SH visit) and 305 in the second row (SH visit). They are both SH visits, so I'd like to have their EPID stay as they are, without being carred over from the previous record. Your code would have the first one 300 carried over to the second row. If you have a quick fix, it's much appreciated. (I'm studying your code now and hope I can find a way to address this).

 

2 A 0 300 300 2011 2013 SH 1 300 0 300
2 A 0 305 300 2013 2013 SH 1 300 0 305
2 B 1 410 410 2014 2014 LS 2 410 1 410

PROC Star
Posts: 7,474

Re: Create seq ID, retain values until an event happens

Again, while I still don't think I understand your specs, adding that condition is easy:

data have;
   input ID 1  fac_code $ 3 epID 6-8 entry_dt 10-13 disch_dt 15-19 vst_type $ 21-22
         out1_facseq 27 out1_epID2  30-32 out2_facseq 36 out2_epID2 39-41;
   datalines;
1 A  110 2011 2012  LS    1  110   1  110
1 A  110 2012 2014  LS    1  110   1  110
1 A  110 2014 2015  LS    1  110   1  110
1 B  120 2015 2016  LS    2  120   2  120
2 A  300 2011 2013  SH    1  300   0  300
2 A  305 2013 2013  SH    1  300   0  305
2 B  410 2014 2014  LS    2  410   1  410
3 A  220 2011 2013  SH    1  220   0  220
3 A  220 2013 2013  SH    1  220   0  220
3 A  250 2014 2015  LS    1  220   1  250
3 B  300 2015 2016  LS    2  300   2  300
4 A  505 2011 2013  SH    1  505   0  505
4 B  620 2013 2013  LS    2  620   1  620
4 B  620 2014 2015  LS    2  620   1  620
4 A  505 2015 2016  SH    3  505   2  505
;

*step 1 - assign fac seq;
proc sort data=have out=have;
     by ID entry_dt disch_dt;
run;
data have (drop=last_vst_type);
    format ID fac_code fac_seq;
    set have;
    by ID fac_code notsorted;
    retain fac_seq;
    if first.ID then fac_seq=0;
    last_vst_type=lag(vst_type);
    fac_seq=ifn((lag(fac_code) ne fac_code and not first.id) or
          (first.fac_code and vst_type in ("LS") or
          (not first.id and vst_type in ("LS") and vst_type ne last_vst_type)),
          fac_seq+1,fac_seq);
run;

*2 -create EPID2;
proc sort data=have;
 by ID fac_code fac_seq entry_dt;
run;

data want;
    format ID fac_code fac_seq epID epID2;
    set have;
    by ID fac_code fac_seq entry_dt;
    retain epID2;
    last_vst_type=lag(vst_type);
    epID2=ifn(lag(fac_code) ne fac_code or first.fac_seq
     or (not first.id and vst_type eq 'SH' and last_vst_type eq 'SH'),epID,epID2);
run;

proc sort data=want;
 by ID entry_dt;
run;

Art, CEO, AnalystFinder.com

Ask a Question
Discussion stats
  • 3 replies
  • 133 views
  • 0 likes
  • 2 in conversation