BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

mark flag based on date difference

hi

i have to give flag "Y" if variable var=2 for two or more consecutive days based on vairable date (Y for each record per ID if the condition is met)

example

 ID var date flag 001 2 24FEB2021 Y 001 2 25FEB2021 Y 001 2 26FEB2021 y 002 2 24FEB2021 002 1 25FEB2021

thank you for your help

1 ACCEPTED SOLUTION

Accepted Solutions
Rhodochrosite | Level 12

Re: mark flag based on date difference

OK.

You could process consecutive days in ascending order, do the same process in descending order, and then return to ascending order.

There's some sorting involved, but it's simple.

``````data have;
length id \$3 var 8 date 8;
format date date9.;
input id var date date9.;
datalines;
001 2 24FEB2021
001 2 25FEB2021
001 2 26FEB2021
002 2 24FEB2021
002 1 25FEB2021
;
run;

proc sort data=have;
by id date;
run;

data next(drop=dt v);
set have;
by id date;
length flag \$1;
retain dt v;
if first.id then dt=0;
if dt+1=date and v=2 and v=var then flag='Y';
dt=date;
v=var;
run;

proc sort data=next;
by id descending date;
run;

data want(drop=dt v);
set next;
by id descending date;
retain dt v;
if first.id then dt=0;
if dt-1=date and v=2 and v=var then flag='Y';
dt=date;
v=var;
run;

proc sort data=want;
by id date;
run;

``````
7 REPLIES 7
Tourmaline | Level 20

Re: mark flag based on date difference

What have you tried do far?

I would use a data step, with BY ID VAR.

You can figure out if there are more than one observation for your by group by using IF NOT FIRST.VAR AND VAR = 2 THEN...

Data never sleeps
Jade | Level 19

Re: mark flag based on date difference

What have you tried so far?

Questions like yours have been asked multiple, so you may want to read the answer given and try to adopt them to your needs.

Obsidian | Level 7

Re: mark flag based on date difference

if you don't want to help why are you posting ? I've tried to calculate time difference between them and then mark the flag but I don't know how can I check if it is consecutive, or to give flag for the first observation
Super User

Re: mark flag based on date difference

@Jedrzej wrote:
if you don't want to help why are you posting ? I've tried to calculate time difference between them and then mark the flag but I don't know how can I check if it is consecutive, or to give flag for the first observation

Please show us your code, as we have gotten quite wary of freeloaders trying to cheat on their homework or similar.

We want to help, but we're not in the business of doing other's work for free. If you need a consultant's work, hire one.

But here's a code example using a double DO loop:

``````data have;
input id \$ var date :date9.;
format date yymmdd10.;
datalines;
001 2 24FEB2021
001 2 25FEB2021
001 2 26FEB2021
002 2 24FEB2021
002 1 25FEB2021
;

data want;
do until(last.id);
set have;
by id;
if date - lag(date) = 1 and lag(var) = 2 and var = 2 and not first.id
then flag = "Y";
end;
do until (last.id);
set have;
by id;
output;
end;
run;``````
Rhodochrosite | Level 12

Re: mark flag based on date difference

ID=001, date=24FEB2021 is the first observation of ID=001, so there are no consecutive days, and I don't think the Y flag will be raised.

``````data have;
length id \$3 var 8 date 8;
format date date9.;
input id var date date9.;
datalines;
001 2 24FEB2021
001 2 25FEB2021
001 2 26FEB2021
002 2 24FEB2021
002 1 25FEB2021
;
run;

proc sort data=have;
by id date;
run;

data want(drop=dt);
set have;
by id date;
length flag \$1;
retain dt;
if first.id then dt=0;
flag=ifc(dt+1=date,'Y','');
dt=date;
run;
``````
Obsidian | Level 7

Re: mark flag based on date difference

no, it's wrong .
you should have "Y" in ID001 24feb2021 cause the question is "if var is 2 for two or more consecutive days, mark all observations within this ID as flag="Y";
and you have flag on ID002 25FEB2021 and it shouldn't be cause var=1
Rhodochrosite | Level 12

Re: mark flag based on date difference

OK.

You could process consecutive days in ascending order, do the same process in descending order, and then return to ascending order.

There's some sorting involved, but it's simple.

``````data have;
length id \$3 var 8 date 8;
format date date9.;
input id var date date9.;
datalines;
001 2 24FEB2021
001 2 25FEB2021
001 2 26FEB2021
002 2 24FEB2021
002 1 25FEB2021
;
run;

proc sort data=have;
by id date;
run;

data next(drop=dt v);
set have;
by id date;
length flag \$1;
retain dt v;
if first.id then dt=0;
if dt+1=date and v=2 and v=var then flag='Y';
dt=date;
v=var;
run;

proc sort data=next;
by id descending date;
run;

data want(drop=dt v);
set next;
by id descending date;
retain dt v;
if first.id then dt=0;
if dt-1=date and v=2 and v=var then flag='Y';
dt=date;
v=var;
run;

proc sort data=want;
by id date;
run;

``````
Discussion stats
• 7 replies
• 1280 views
• 2 likes
• 5 in conversation