Help using Base SAS procedures

Grouping by consecutive days (SQL)

Reply
Super Contributor
Posts: 316

Grouping by consecutive days (SQL)

Hi,

Any idea how to tagged dates based on consecutive days using sql? Kindly see example below. I know this can be attained using data step using first and last and lag. But on SQL how can I do this? Greatly appreciate your help. Thanks!

Data:

Date
01jan2014
02jan2014
03jan2014
07jan2014
08jan2014
10jan2014
12jan2014
13jan2014

Result:

DatePeriod
01jan20141
02jan20141
03jan20141
07jan20142
08jan20142
10jan20143
12jan20144
13jan20144
Super User
Posts: 17,750

Re: Grouping by consecutive days (SQL)

I'm sure it can be somehow but why? There is no value to doing something like this via SQL.

If you're in a DBMS environment such as MS SQL then I'd use a cursor.

Super Contributor
Posts: 316

Re: Grouping by consecutive days (SQL)


For a given period, the analysis should be done for every consecutive days for a given customer, given that my extract period is 1 month, i played 1st to 4th day, 6th day, 10th to 15th day. this will result to 3 different periods.

Respected Advisor
Posts: 4,641

Re: Grouping by consecutive days (SQL)

I hope you are not looking for efficiency. Using the wrong tool :

data dates;

input date :date9.;

format date date9.;

datalines;

01jan2014

02jan2014

03jan2014

07jan2014

08jan2014

10jan2014

12jan2014

13jan2014

;

proc sql;

create table gaps as

select date as gap

from dates

where intnx("DAY",date,-1) not in (select date from dates);

create table want as

select date, (select count(*) from gaps where gap<=date) as period

from dates;

drop table gaps;

select * from want;

quit;

PG

PG
Super Contributor
Posts: 316

Re: Grouping by consecutive days (SQL)

Thanks for your response. However I am looking into performance as well since the SQL script should be explicitly pass-through to greenplum.

Also, this will be done for every customerid.

Super User
Posts: 17,750

Re: Grouping by consecutive days (SQL)

Then you should write your entire query in GreenPlum SQL.

You might be better trying a SQL specific forum instead of this one.

Ask a Question
Discussion stats
  • 5 replies
  • 563 views
  • 1 like
  • 3 in conversation