Create a running counter based on ID and date-CodePudding

I have 3 variables and a counter has to be created based on them.

   data that I have

ID  window start  window end 
1   29oct20         12mar21
1   31oct20         08Feb21
1   31oct21         08feb21
1   31oct21         08feb21
2   06Nov20         11Apr21
2   06Nov20         11Apr21
2   27Nov20         01Apr19

 data that I want

ID  window start  window end  priority_count
1   29oct20         12mar21      1
1   31oct20         08Feb21      2
1   31oct21         08feb21      2
1   31oct21         08feb21      2
2   06Nov20         11Apr21      1
2   06Nov20         11Apr21      1
2   27Nov20         01Apr19      2

So for every Id a new count should start once a new date comes.

I have been using this code

data want;
set have;
by ID window_start window_end;
if first.ID and first.window_start and first.window_endthen priority_count=1;
else priority_count 1;
run;

but it gives the value as

priority_count
1
2
3
4
1
2
3

Any help will be appreciated.

CodePudding user response：

Not sure if those are typos but there are several observations for which window_start is after window_end.

Using the LAG function

data want;
    set have;
    by id;
    _lag=lag(window_start);

    if first.id then priority_count=1;
    else do;
        if window_start ne _lag then
            priority_count   1;
    end;
    drop _lag;
run;

 ID window_start window_end priority_count
 1  29OCT2020    12MAR2021        1
 1  31OCT2020    08FEB2021        2
 1  31OCT2020    08FEB2021        2
 1  31OCT2020    08FEB2021        2
 2  06NOV2020    11APR2021        1
 2  06NOV2020    11APR2021        1
 2  27NOV2020    01APR2019        2

CodePudding user response：

I think you're on the right track but need a slight modifications on your IF statements to reflect the logic.

Set to 0 at first of each ID
Increment if the window_end changes (or window_start since they're consistent in your example). Setting it to 0 initially means you can increment without worrying if it's the first or not.

data want;
set have;
by ID window_start window_end;
if first.ID then priority_count=0;

if first.window_end then priority_count 1;

run;