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!
... View more
