topic Re: mark flag based on date difference in SAS Programming

mark flag based on date difference

Jedrzej — Wed, 24 Feb 2021 07:53:42 GMT

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

Re: mark flag based on date difference

LinusH — Wed, 24 Feb 2021 08:16:03 GMT

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...

Re: mark flag based on date difference

andreas_lds — Wed, 24 Feb 2021 08:17:34 GMT

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.

Re: mark flag based on date difference

Jedrzej — Wed, 24 Feb 2021 08:27:18 GMT

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

Re: mark flag based on date difference

japelin — Wed, 24 Feb 2021 08:29:41 GMT

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;

Re: mark flag based on date difference

Jedrzej — Wed, 24 Feb 2021 08:36:02 GMT

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

Re: mark flag based on date difference

japelin — Wed, 24 Feb 2021 08:42:39 GMT

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;

Re: mark flag based on date difference

Kurt_Bremser — Wed, 24 Feb 2021 09:15:35 GMT

@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;