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:
Date | Period |
---|---|
01jan2014 | 1 |
02jan2014 | 1 |
03jan2014 | 1 |
07jan2014 | 2 |
08jan2014 | 2 |
10jan2014 | 3 |
12jan2014 | 4 |
13jan2014 | 4 |
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.
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.
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
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.
Then you should write your entire query in GreenPlum SQL.
You might be better trying a SQL specific forum instead of this one.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.