flagging previous events

[darb]

Here is what my data looks like:

Company	Project	Date	Win_Lose	Flag Needed (previous win ever)
A	Project1	date1	L	N
A	Project2	date2	W	N
A	Project3	date3	L	Y
A	Project4	date4	W	Y
A	Project5	date4	W	Y
A	Project6	date5	L	Y
A	Project7	date6	W	Y
B	Project1	date1	L	N
B	Project2	date2	L	N
B	Project3	date3	W	N
B	Project4	date4	L	Y

I am trying to create flag that shows for each project if there was a winning project within the same company anytime prior to that project. I was trying to use lag() but that only help with the project directly preceding the one in question. And yes i meant to write date4 twice. Some times there are multiple projects at same date.

Thank you!

D

[Astounding]

The fact that two projects can have the same date (when one is a win and one is a loss) complicates things just a little.  Here's a way to overcome that, assuming your data set is already sorted:

data want;

do until (last.company);

   set have;

   by company;

   if win_lose='W' and first_win_date=. then first_win_date = date;

end;

do until (last.company);

   set have;

   by company;

   if date <= first_win_date then flag='N';

   else flag='Y';

   output;

end;

drop first_win_date;

run;

[darb]

The data came out really wonky...confused as to where the first_win_date is coming from...Thanks!

[Astounding]

Wonky?  Should be perfect, as long as the data coming in is sorted BY COMPANY DATE.

Perhaps if you were to show the log of your program, it could be debugged.

[darb]

So this is the code I used:

proc sort data = test4;
by account_name created_date;
run;

data test5; do until (last.account_name);
set test4;
by account_name;
if stage = "Closed Won" and first_win_date =. then first_win_date = created_date;
end;
do until (last.account_name);
set test4;
by account_name;
if created_date<=first_win_date then flag="N";
else flag ="Y";
output;
end;
drop first_win_date;
run;

and i attached some output...you can see the flags dont make sense with regards closed lost or closed won. the count variable indicates if is the same company or not (1 means new company, 2,3....means diff project same company). The log doesnt find any issues.

[Astounding]

OK, let's try a slight variation.  This statement gets replaced:

if created_date<=first_win_date then flag="N";

Instead, try it this way:

if first_win_date=. or created_date<=first_win_date then flag="N";

Also note, if your real data set is large this can be rolled up into one loop instead of two.  First step, though, is to get the logic working.

As long as everybody else is updating their solutions, here's my update:

data test5;

do until (last.account_name);
   set test4;
   by account_name;
   if stage = "Closed Won" and first_win_date =. then first_win_date = created_date;

   if (first_win_date = . ) or (created_date <= first_win_date) then flag='N';

   else flag='Y';

   output;
end;

run;

[darb]

thank you! all good 🙂

[art297]

The following should work:

data want (drop=hold);
  set have;
  by Company;
  retain hold;
  if first.Company then hold='N';
  Flag=hold;
  output;
  if Win_Lose eq 'W' then hold='Y';
run;

Art, CEO, AnalystFinder.com

[darb]

This almost worked completely! However, there are instances where there are only two projects but the dates are the same:

I attached the data, you can see the last two observations have the same date, and they are the same company (you can't see that part) with no other previous projects. The first flag is N, and the second flag is Y, but they should both be N.

[jdwaterman91]

I would first make sure your data is sorted by Company and then by Date in case there are instances where you have two projects with the same date and one is a "W" and the other is an "L"

For your problem, if you use a retain statement in your data step then your lag() function will work in the way you want it to.

Running this should give you your desired output

proc sort data=have;
by company date;
run;

data want;
set have;
by Company;
Retain flag;
if first.Company then flag = "N";
If lag(Win_Lose) = "W" then flag = "Y";
run;

[darb]

It's my understanding that lag only looks at the most previous observation and not all previous observations? is that true in this case?

[jdwaterman91]

Yes. Flag will equal "N" when the it is the first observation for a company and the retain statement will cause flag to equal "N" until a "W" is encountered for the previous Win_Lose value.  Then the retain statement will cause flag to equal "Y" until the evaluating the data for a different company.

[darb]

that worked as well, just my only problem is that sometimes there are multiple projects on the same date with no prior projects (so it logs the first date as N, but then the next ones on the same date as Y, when in fact they should all be N

[art297 · Accepted solution]

I think the following will account for your same date criterion. You hadn't mentioned that in your initial post:

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

Note: above code modified 26Jun2017 at 4:20p.m.

[Astounding]

Art, despite having a correctly marked solution, you might have some tweaking to do.  Try your program with this data:

data test;

company='A';

do date = 1, 5;

do win_lose = 'W', 'L';

output;

end;

end;

run;