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?
You can use the method called "Look-Ahead and Look-Back" described in Look-Ahead and Look-Back - sasCommunity
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;
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
But that would also mark 2 successive empty flag1 with flag2 = 'Y'.
Yes, but that is easily correctible:
if (flag1='Y') and not (first.flag1 and last.flag1) then flag2='Y';
Astounding have my word . Thanks, Astounding .
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.
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.
Thanks, I did change that once I noticed.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.