How to identify consecutive dates in SAS

[Mchan890]

I have the SQL query below that works in Oracle TOAD during testing to identify number of consecutive days for transactions by employee id. However, I need to incorporate this query into SAS to read of a SASdata set. Would someone know how to convert the SQL below to run in SAS? Thanks!

select y.emplid, count(*) as Num_ConsecDays
from (
select x.emplid,x.trans_dt, sum(changed) over (partition by x.emplid order by x.trans_dt) sum_changed
from (
select a.emplid, b.trans_dt,
(case when lag(b.trans_dt) over (partition by a.emplid order by b.trans_dt) - b.trans_dt = -1
then 0 else 1 end ) as changed
from ps_ex_sheet_hdr a, ps_ex_sheet_line b
where b.trans_dt >= to_date('12/1/2019 00:00:00', 'mm/dd/yyyy hh24:mi:ss')
and b.trans_dt <= to_date('1/27/2020 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
and a.sheet_id = b.sheet_id
and a.emplid = '300717')x )y
group by y.emplid, sum_changed;

[ed_sas_member]

Hi @Mchan890 

I am not sure if that answers your question but here is a way to access to Oracle and copy-paste your SQL query:

proc sql;
	connect to oracle (/*<your options such as user= password= path= ... to access DBMS>*/);
	select * from connection to oracle (/*<your SQL query>*/);
	disconnect from oracle;
quit;

or

libname mylib oracle /*<your options such as user= password= path= ...>*/;

proc fedsql;
	/*<your query in native SQL language. Use the libref 'mylib.' to access the datasets>*/;
quit;

 Hope this help,

Best,

[Mchan890]

Thanks for t he feedback! The main data (ps_ex_sheet_hdr, ps_ex_sheet_line) are pulled with Oracle connection in SAS. I needed to convert the query to a Proc SQL on a subset of data in SAS.

[Tom]

Since your SQL is using syntax (windowing functions) not supported by PROC SQL you need to provide a description of what it is doing.

Provide example input and output data.  Make sure to provide them in the form of a data step that people that want to help you can copy and run to create your sample data.  Also make sure to use the Insert Code or Insert SAS code buttons on the Forum editor when pasting (or typing) in code or data so the forum doesn't distort the layout because it thinks they are paragraphs. 

[Mchan890]

Pretty much I have a dataset of emplid and trans_dt and I need to flag when there is 'changed' to identify consecutive days. I would eventually sum this data by emplid to determine how many consecutive transaction dates an employee have. 

EMPLID	TRANS_DT	CHANGED
300717	12/10/2019	1
300717	12/11/2019	0
300717	12/11/2019	1
300717	12/12/2019	0
300717	12/16/2019	1
300717	12/17/2019	0
300717	12/18/2019	0
300717	12/19/2019	0
300717	12/20/2019	0
300717	12/30/2019	1
300717	12/31/2019	0
300717	1/2/2020	1
300717	1/3/2020	0
300717	1/8/2020	1
300717	1/9/2020	0
300717	1/10/2020	0
300717	1/13/2020	1
300717	1/14/2020	0
300717	1/15/2020	0
300717	1/16/2020	0
300717	1/17/2020	0
300717	1/20/2020	1
300717	1/21/2020	0
300717	1/22/2020	0
300717	1/23/2020	0
300717	1/24/2020	0

[Tom · Accepted solution]

I cannot tell from your statement or example report what you have or what you want.

You talking about consecutive dates.  Are you trying to check if there are gaps between two dates?

data have;
  input EMPLID $ TRANS_DT :mmddyy. CHANGED ;
  format TRANS_DT yymmdd10.;
cards;
300717 12/10/2019 1
300717 12/11/2019 0
300717 12/11/2019 1
300717 12/12/2019 0
300717 12/16/2019 1
300717 12/17/2019 0
300717 12/18/2019 0
300717 12/19/2019 0
300717 12/20/2019 0
300717 12/30/2019 1
300717 12/31/2019 0
300717 1/2/2020 1
300717 1/3/2020 0
300717 1/8/2020 1
300717 1/9/2020 0
300717 1/10/2020 0
300717 1/13/2020 1
300717 1/14/2020 0
300717 1/15/2020 0
300717 1/16/2020 0
300717 1/17/2020 0
300717 1/20/2020 1
300717 1/21/2020 0
300717 1/22/2020 0
300717 1/23/2020 0
300717 1/24/2020 0
;

data want;
  set have;
  by EMPLID trans_dt ;
  gap = dif(trans_dt) > 1;
  if first.EMPLID then gap=0;
run;

Obs    EMPLID      TRANS_DT    CHANGED    gap
  1    300717    2019-12-10       1        0
  2    300717    2019-12-11       0        0
  3    300717    2019-12-11       1        0
  4    300717    2019-12-12       0        0
  5    300717    2019-12-16       1        1
  6    300717    2019-12-17       0        0
  7    300717    2019-12-18       0        0
  8    300717    2019-12-19       0        0
  9    300717    2019-12-20       0        0
 10    300717    2019-12-30       1        1
 11    300717    2019-12-31       0        0
 12    300717    2020-01-02       1        1
 13    300717    2020-01-03       0        0
 14    300717    2020-01-08       1        1
 15    300717    2020-01-09       0        0
 16    300717    2020-01-10       0        0
 17    300717    2020-01-13       1        1
 18    300717    2020-01-14       0        0
 19    300717    2020-01-15       0        0
 20    300717    2020-01-16       0        0
 21    300717    2020-01-17       0        0
 22    300717    2020-01-20       1        1
 23    300717    2020-01-21       0        0
 24    300717    2020-01-22       0        0
 25    300717    2020-01-23       0        0
 26    300717    2020-01-24       0        0