Ok. Try making this adjustment and see if that fixes your problem.
proc sort data=have;
by company date;
run;
data have;
set want;
by Company;
Retain flag;
If first.Company then flag = "N";
If (lag(Win_Lose) = "W" and lag(date) ^= date and lag(company) = company) then flag = "Y";
run;
@Astounding: Good catch. I had to make a slight change to your example because, according to the original specs, date is a character variable. If it turns out to be a numeric variable, the length statement in the following would have to be changed.
@darb: @Astounding was correct and my suggested code wouldn't correctly capture all conditions. I think that the following will and I will adjust my original code accordingly:
data want (drop=_:); set have; by Company; length _got_w $1; length _first_date $8; retain _got_W _first_date; if first.Company then do; _got_W='N'; end; if _got_W eq 'N' and Win_Lose eq 'W' then do; _got_W='Y'; _first_date=date; end; Flag=ifc(date gt _first_date and _got_W eq 'Y','Y','N'); output; run;
Art, CEO, AnalystFinder.com
Assuming I understand your quesiton.
data have;
input (Company Project Date Win_Lose) (: $20.);
cards;
A Project1 date1 L
A Project2 date2 W
A Project3 date3 L
A Project4 date4 W
A Project5 date4 W
A Project6 date5 L
A Project7 date6 W
B Project1 date1 L
B Project2 date2 L
B Project3 date3 W
B Project4 date4 L
;
run;
data want;
set have;
by company;
retain found 0 flag 'N';
if first.company then do;found=0;flag='N';end;
if lag(Win_Lose)='W' and company=lag(company) and not found then do;
flag='Y';found=1;
end;
drop found;
run;
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.