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 |
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:
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;
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:
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.