Next-Prior logic in Column Matching

[sasuser_sk]

Hello:

I use next/prior logic in Brio and looking to build same logic in SAS. I'm looking to create column Next Match(below) and Prior match. Here is my what I asking for help with:

Next match:

if(Duplicate=0 and (End_Date=Next(Create_date))) then 'No Break'
elseif(ID_No=Next(ID_No) and (End_Date != Next(Create_date))) then'After few Days'

Prior match:

if(ID_No=Prior(ID_No) and (Create_date=Prior(End_Date))) then 'No Break'
else if(ID_No=Prior(ID_No) and (Create_date != Prior(End_Date))) then 'After few Days'

Thank you!

Acct No	ID No	Duplicate	Create date	End Date	Next Match	Prior Match
486	486	1	20201106	20201113
177	177	1	20210105	20210111
126	126	1	20200812	20201027
651	651-001	0	20200923	20200928	No Break
651	651-001	1	20200928	20210119		No Break
390	390	1	20201208	20201214
229	229	1	20201112	20201211
411	411	1	20200804	20200814
674	674-001	0	20201009	20201104	No Break
674	647-001	1	20201104	20201105		No Break
681	681	1	20201023	20201113
19	19	0	20200730	20200819	No Break
19	19	1	20200819	20201105		No Break

[mkeintz · Accepted solution]

This is untested since you have not put your sample data into the form of a working sas data step (translation: help us help you).

Let's assume:

1. you have a dataset named HAVE with variable names corresponding to your table header.
2. dataset HAVE is grouped (not necessarily in ascending or descending order) by ID_NO.
3. Whenever duplicate=0 there is always a subsequent record with the same ID_NO (I infer that from your rule statement):
     if(Duplicate=0 and (End_Date=Next(Create_date))) then 'No Break'

Then this sas program is untested, but shows the logic as I understand your rules:

data want (drop=_:);
  set have (keep=id_no);
  by id_no notsorted;    /* Added the notsorted, edit*/

  merge have have (firstobs=2 keep=create_date rename=(create_date=nxt_crdate));
  
  length newvar newvar_prior $20; /* Also editted addition */
  if duplicate=0 and end_date=nxt_crdate then newvar='No Break';
  else if last.id_no=0 and end_date^=nxt_crdate then newvar='After few Days';

  _lag_end_date=lag1(end_date);
  if first.id_no=0 then do;
     if create_date=_lag_end_date then newvar_prior='No Break';
     else newvar_prior='After few Days';
  end;
run;

1. The SET HAVE statement reads only one variable, ID_NO.  It is succeeded with a BY ID_NO NOTSORTED statement, which tells sas to create two temporary dummy variables (first.id_no and last.id_no) indicating whether the observation-in-hand is the first and/or the last for the current ID_NO.
   
   
2. The self-merge statement reads all the variables from the current OBS and one variable (create_date renamed to nxt_crdate) from the next obs.  This provides a way to look ahead at the next date.
   
   
3. The "if duplicate=0 ..." and "else if last.id_no=0 ..." statements imitates your rule descriptions.
   
   
4. The lag1(end_date) function creates a fifo queue (1 level deep) that, in this case, returns the prior end_date value.  
   
   
5. The second pair of "if ...",  "else if" imitate your rule descriptions.

[sasuser_sk]

Thank you! It worked fine.

I'm unsure on what format is the sample data accepted into SAS Community  so that others could use it for programming purposes. I copy/pasted from Excel.