## Data Logic

Occasional Contributor
Posts: 12

# Data Logic

All,

I have the following data structure .

ID   time(hr)   flag1   flag2

11    2           Y

11    4           Y

11    6

11    8           Y

11   10

22   2            Y

22   4

22   6            Y

22   8            Y

22   10           Y

If 2 or more consecutive time points(time) have a "Y " for flag1 , then I am trying to assig the "Y" to flag2  field as below

The output I am looking for is

ID   time(hr) flag1    flag2

11    2         Y         Y

11    4         Y         Y

11    6

11    8         Y

11    10

22    2         Y

22    4

22    6         Y         Y

22    8         Y         Y

22    10        Y         Y

can anyone help me with the logic?

Valued Guide
Posts: 580

## Re: Data Logic

You can use the method called "Look-Ahead and Look-Back" described in Look-Ahead and Look-Back - sasCommunity

Super User
Posts: 10,278

## Re: Data Logic

I came to the following solution:

data comp (keep=id h1 flag2 rename=(h1=time));

set have;

by id;

retain flag2 'Y';

flag1_l1 = lag(flag1);

flag1_l2 = lag2(flag1);

time_l1 = lag(time);

id_l1 = lag(id);

id_l2 = lag2(id);

if not first.id then do;

if flag1 = 'Y' and flag1_l1 = 'Y' then do;

if flag1_l2 ne 'Y' and (id_l2 = id or id_l2 = .) then do;

h1 = time_l1;

output;

end;

h1 = time;

output;

end;

end;

run;

data want;

merge have comp;

by id time;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,784

## Re: Data Logic

If I understood your question rightly.

```data have;
infile cards truncover;
input ID   time   flag1  \$ ;
cards;
11    2           Y
11    4           Y
11    6
11    8           Y
11   10
22   2            Y
22   4
22   6            Y
22   8            Y
22   10           Y
;
run;
data want;
set have;
by id flag1 notsorted;
if not (first.flag1 and last.flag1) then flag2='Y';
run;
```

Xia Keshan

Super User
Posts: 10,278

## Re: Data Logic

But that would also mark 2 successive empty flag1 with flag2 = 'Y'.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,785

## Re: Data Logic

Yes, but that is easily correctible:

if (flag1='Y') and not (first.flag1 and last.flag1) then flag2='Y';

Super User
Posts: 10,784

## Re: Data Logic

Astounding have my word . Thanks, Astounding .

Super User
Posts: 6,785

## Re: Data Logic

A nice problem to start the week.  One approach:

data want;

yes_count=0;

do until (last.id or flag1=' ');
set have;

by id;

if flag1='Y' then yes_count + 1;

end;

do until (last.id or flag1=' ');

set have;

by id;

if flag1='Y' and yes_count >= 2 then flag2='Y';

else flag2=' ';

output;

end;

drop yes_count;

run;

The top loop reads a bunch of records, and determines when FLAG2 should be Y for this records.  The bottom loop reads the same records and assigns FLAG2.

Good luck.

Super User
Posts: 10,278

## Re: Data Logic

I knew there was a DWO loop solution, just couldn't get myself to  find it!

But you need to compare >=2, or you will only catch three Ys in succession

Edit: saw you already fixed it.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,785

## Re: Data Logic

Thanks, I did change that once I noticed.

Posts: 3,167

## Re: Data Logic

OK, look ahead and look back:

data have;

infile cards truncover;

input id time flag \$;

cards;

11    2 Y

11    4 Y

11    6

11    8 Y

11   10

22   2 Y

22   4

22   6 Y

22   8 Y

22   10 Y

;

data want;

set have;

by id;

set have(firstobs=2 keep=flag rename=flag=_f) have(obs=1 drop=_all_);

if not last.id and flag='Y' and _f='Y' or flag='Y' and lag(flag)='Y' then

flag2='Y';

drop _f;

run;

Haikuo

Discussion stats
• 10 replies
• 508 views
• 1 like
• 6 in conversation