data test;
input id$2. start end ;
attrib start format =date9. informat=date9.;
attrib end format =date9. informat=date9.;
datalines;
1 01JAN2015 14FEB2015
1 18FEB2015 30APR2015
2 01jan2015 28JAN2015
2 01apr2015 30apr2015
3 01JAN2015 14FEB2015
3 15FEB2015 15MAR2015
3 20MAR2015 30APR2015
;
run;
I have the above data, there are multiple records per id I want to apply the following criteria:
-keep all records with more than 60 days gap between end of one period and the start of the other
-keep only the first record if if the end dat of one period and the start of ther others are less than 60 days
2 01apr2015 30apr2015
Output
1 01JAN2015 14FEB2015
2 01jan2015 28JAN2015
2 01apr2015 30apr2015
3 01JAN2015 14FEB2015
data test;
input id$2. start end ;
attrib start format =date9. informat=date9.;
attrib end format =date9. informat=date9.;
datalines;
1 01JAN2015 14FEB2015
1 18FEB2015 30APR2015
2 01jan2015 28JAN2015
2 01apr2015 30apr2015
3 01JAN2015 14FEB2015
3 15FEB2015 15MAR2015
3 20MAR2015 30APR2015
;
run;
data want;
set test;
by id;
l=lag(end);
if first.id then f=1;
else do;
if start-l>60 then f=1;
else f=0;
end;
if f;
drop l f;
run;
data test;
input id$2. start end ;
attrib start format =date9. informat=date9.;
attrib end format =date9. informat=date9.;
datalines;
1 01JAN2015 14FEB2015
1 18FEB2015 30APR2015
2 01jan2015 28JAN2015
2 01apr2015 30apr2015
3 01JAN2015 14FEB2015
3 15FEB2015 15MAR2015
3 20MAR2015 30APR2015
;
run;
data want;
set test;
by id;
l=lag(end);
if first.id then f=1;
else do;
if start-l>60 then f=1;
else f=0;
end;
if f;
drop l f;
run;
Thank you!
Hi @lillymaginta You are welcome. Plus, Thank you for posting the sample HAVE and WANT clearly. That makes it so much convenient.
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.