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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.