BookmarkSubscribeRSS Feed
Shuail_Ibrahim
Calcite | Level 5

I have a dataset which has index and date  I need to perform logic in which I need to compare the first value with next date if the date range is lesser than 31 remove the second, again compare the first with third if it's greater than 31 keep the first and third and compare the third with fourth if less remove or if greater add keep on like this. please help me out with a SAS or SQL query 

 

dataset 

dataset 
index     DATE
1     2020-01-01
1     2020-01-15
1     2020-01-30
1     2020-02-02
1     2020-02-20
1     2020-03-05
1     2020-03-25
2     2020-04-30


required dataset 
1     2020-01-01
1     2020-02-02
1     2020-03-05
2     2020-04-30
6 REPLIES 6
Reeza
Super User
Do you just want the first record for every month or is it more complicated than that?
Shuail_Ibrahim
Calcite | Level 5
if its min date range from each month this query works, but I need to compare between each date a span of 31...

select index, MIN(date) from indextbl
group by index, LEFT(date,7)
Reeza
Super User
*make fake data;
data have;
input index date : yymmdd10.;
format date date9.;
cards;
1     2020-01-01
1     2020-01-15
1     2020-01-30
1     2020-02-02
1     2020-02-20
1     2020-03-05
1     2020-03-25
2     2020-04-30
;;;;
run;

proc sort data=have;
by index date;
run;

*keep the first record of each person/month;
data want;
set have;
by index date groupformat;
format date yymm6.;
if first.date;
run;

*format the date back to yymmdd format for display;
proc datasets lib=work nodetails nolist;
modify want;
format date yymmddd10.;
run;

*display results;
proc print data=want;
run;

If so, use the GROUPFORMAT option and format your date as a year month format and SAS will pick the first record for every month.

Shuail_Ibrahim
Calcite | Level 5
Span of 31 days gap fails in the above
Reeza
Super User
So the rule is all dates need to be 31 days or more apart? Your logic isn't specified clearly:
"again compare the first with third if it's greater than 31 keep the first and third and compare the third with fourth if less remove or if greater add keep on like this."

It seems like you're comparing to both the first and the prior records?
Shuail_Ibrahim
Calcite | Level 5
Correct compare it with first record and follow on with others

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1544 views
  • 0 likes
  • 2 in conversation