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
*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.
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!
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.
Ready to level-up your skills? Choose your own adventure.