PROC SQL, Recursive Common Table Expression, dealing with consecutive days

[iced_tea]

Hello Community,

Trying something new where I am reusing the 1st solution from the following: https://stackoverflow.com/questions/15783315/combine-consecutive-date-ranges by modifying it to be in PROC SQL instead of SQL Server. This is what they have:

;with cte as (
   select a.employmentid, a.startdate, a.enddate
     from Tbl a
left join Tbl b on a.employmentid=b.employmentid and a.startdate-1=b.enddate
    where b.employmentid is null
    union all
   select a.employmentid, a.startdate, b.enddate
     from cte a
     join Tbl b on a.employmentid=b.employmentid and b.startdate-1=a.enddate
)
   select employmentid,
          startdate,
          nullif(max(isnull(enddate,'32121231')),'32121231') enddate
     from cte
 group by employmentid, startdate
 order by employmentid

  This is how I've attempted to modify it:

proc sql;
create view cte as (
   select a.employmentid, a.startdate, a.enddate
     from Tbl a
left join Tbl b on a.employmentid=b.employmentid and a.startdate-1=b.enddate
    where b.employmentid is null
    union all
   select a.employmentid, a.startdate, b.enddate
     from cte a
     join Tbl b on a.employmentid=b.employmentid and b.startdate-1=a.enddate
)
   select employmentid,
          startdate,
          nullif(max(isnull(enddate,'32121231')),'32121231') enddate
     from cte
 group by employmentid, startdate
 order by employmentid;
quit;

SAS doesn't like the last select piece. "Syntax error, expecting one of the following: EXCEPT, INTERSECT, UNION."

Any ideas as to what I'm doing wrong here? I think it is something to do with not translating SQL's  WITH clause correctly.

Thank you!

[SASKiwi]

This isn't valid SAS SQL:

   select employmentid,
          startdate,
          nullif(max(isnull(enddate,'32121231')),'32121231') enddate
     from cte

I suggest you just try this:

   select employmentid,
          startdate,
          enddate
     from cte

If you need to deal with missing values you can always do it afterwards or if necessary use an equivalent CASE statement.

[iced_tea]

Thank you for taking a look. Dropping the nullif piece didn't work as it gives the same error. The last SELECT is underlined and the message reads:
"Syntax error, expecting one of the following: EXCEPT, INTERSECT, UNION."

[Kurt_Bremser]

Start with this:

proc sql;
create view v_cte as
  select a.employmentid, a.startdate, a.enddate
  from Tbl a
  left join Tbl b on a.employmentid=b.employmentid and a.startdate-1=b.enddate
  where b.employmentid is null
  union all
  select a.employmentid, a.startdate, b.enddate
     from cte a
     join Tbl b on a.employmentid=b.employmentid and b.startdate-1=a.enddate
;
create table want as
  select
    employmentid,
    startdate,
    enddate
  from v_cte
  group by employmentid, startdate
  order by employmentid
;
quit;

[iced_tea]

Thank you for taking a look Kurt. This produces the following error:

"The current reference to SQL View WORK.v_cte has exceeded PROC SQL's limit of 50 references
to a single view in a SQL statement. Please check to make sure that these references are not
part of a recursive situation."

This means something got stuck in an infinite loop, no?

[Kurt_Bremser]

Make sure that tbl and cte are no views that create a circular reference to themselves.

[iced_tea]

Thank you. Do you know where in the code it is doing a circular reference?

proc sql;
create view v_cte as
  select a.employmentid, a.startdate, a.enddate
  from Tbl a
  left join Tbl b on a.employmentid=b.employmentid and a.startdate-1=b.enddate
  where b.employmentid is null
  union all
  select a.employmentid, a.startdate, b.enddate
     from cte a
     join Tbl b on a.employmentid=b.employmentid and b.startdate-1=a.enddate
;
create table want as
  select
    employmentid,
    startdate,
    enddate
  from v_cte
  group by employmentid, startdate
  order by employmentid
;
quit;

[Kurt_Bremser]

From all I see, this code is not the reason; instead, one of the inputs (CTE and/or TBL) has the issue.

[LinusH]

In SAS SQL, isnull corresponds to coalesce.

Max works in SAS.

nullif can be mimiced as @SASKiwi stated using case when else logic.

[PGStats]

For up to 5 chained periods per sequence, in SAS/SQL you could do (example data from Stackoverflow reference):

data have;
infile datalines dsd;
input id (dStart dEnd) (:yymmdd10.);
format dStart dEnd yymmdd10.;
datalines;
5, 2007-12-03, 2011-08-26
5, 2013-05-02, .
30, 2006-10-02, 2011-01-16
30, 2011-01-17, 2012-08-12
30, 2012-08-13, .
66, 2007-09-24, .
;

proc sql;
select 
    a.id, 
    a.dStart,
    case 
        when e.id then e.dEnd
        when d.id then d.dEnd
        when c.id then c.dEnd
        when b.id then b.dEnd
        else a.dEnd 
    end as dEnd         format=yymmdd10.
from
    have as a left join
    have as b on a.id=b.id and a.dEnd+1=b.dStart left join
    have as c on a.id=c.id and b.dEnd+1=c.dStart left join
    have as d on a.id=d.id and c.dEnd+1=d.dStart left join
    have as e on a.id=e.id and d.dEnd+1=e.dStart
group by a.id, calculated dEnd
having a.dStart = min(a.dStart) ;
quit;