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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.