CODEWITHSUNDEEP
sas
ldm
ldm

Reputation: 1

mimic window function in sas to group rows and calculate the max continuous membership

I have a table like this showing the id of a member, the beginning and end date of their membership, the days of their membership and the gap between their last membership and current membership period.

Im trying to find out the longest spans of a certain member's continuous membership, which is 2160 in this case. I think a window function lag/lead is necessary in SQL. however window function is not supported in sql. how can group these periods based on gap days and calculate the max spans?

thank you for the help! enter image description here

Upvotes: 0

Views: 179

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This question was originally tagged SQL/MySQL/Oracle. This answers the original version of the question.

You can summarize the data for each members for a user. The idea is to sum up the non-zero values of gap (which is rather convenient to have). This defines a group that can be used for aggregation. And, in this case, you can just sum the gap:

select id, sum(span), min(begin_date), max(end_date)
from (select t.*,
             sum(gap) over (partition by id order by begin_date) as grp
      from t
     ) t
group by id, grp;

For only the longest per id, you can use window functions again:

select *
from (select id, sum(span), min(begin_date), max(end_date),
             row_number() over (partition by id order by sum(span) desc) as seqnum
      from (select t.*,
                   sum(gap) over (partition by id order by begin_date) as grp
            from t
           ) t
      group by id, grp
     ) ig
where seqnum = 1;

Upvotes: 0

Related Questions