topic Re: Rearrange dataset based on condition in SAS Programming

Rearrange dataset based on condition

Shuail_Ibrahim — Wed, 15 Jul 2020 15:22:22 GMT

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

Re: Rearrange dataset based on condition

Reeza — Wed, 15 Jul 2020 15:47:26 GMT

Do you just want the first record for every month or is it more complicated than that?

Re: Rearrange dataset based on condition

Reeza — Wed, 15 Jul 2020 15:56:51 GMT

*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.

Re: Rearrange dataset based on condition

Shuail_Ibrahim — Wed, 15 Jul 2020 16:21:48 GMT

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)

Re: Rearrange dataset based on condition

Shuail_Ibrahim — Wed, 15 Jul 2020 16:26:10 GMT

Span of 31 days gap fails in the above

Re: Rearrange dataset based on condition

Reeza — Wed, 15 Jul 2020 16:39:24 GMT

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?

Re: Rearrange dataset based on condition

Shuail_Ibrahim — Wed, 15 Jul 2020 17:39:46 GMT

Correct compare it with first record and follow on with others