flagging previous events

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

flagging previous events

[ Edited ]

Here is what my data looks like:

 

CompanyProjectDateWin_LoseFlag Needed (previous win ever)
AProject1date1LN
AProject2date2WN
AProject3date3LY
AProject4date4WY
AProject5date4WY
AProject6date5LY
AProject7date6WY
BProject1date1LN
BProject2date2LN
BProject3date3WN
BProject4date4LY

 

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


Accepted Solutions
Solution
‎06-26-2017 03:10 PM
PROC Star
Posts: 7,428

Re: flagging previous events

[ Edited ]

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.

View solution in original post


All Replies
Super User
Posts: 5,352

Re: flagging previous events

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;

Occasional Contributor
Posts: 8

Re: flagging previous events

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

Super User
Posts: 5,352

Re: flagging previous events

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.

Occasional Contributor
Posts: 8

Re: flagging previous events

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.


data1.PNG
Super User
Posts: 5,352

Re: flagging previous events

[ Edited ]

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;

Occasional Contributor
Posts: 8

Re: flagging previous events

thank you! all good Smiley Happy
PROC Star
Posts: 7,428

Re: flagging previous events

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

 

Occasional Contributor
Posts: 8

Re: flagging previous events

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.


data.PNG
Contributor
Posts: 22

Re: flagging previous events

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;
Occasional Contributor
Posts: 8

Re: flagging previous events

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

Contributor
Posts: 22

Re: flagging previous events

[ Edited ]

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.

Occasional Contributor
Posts: 8

Re: flagging previous events

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

Solution
‎06-26-2017 03:10 PM
PROC Star
Posts: 7,428

Re: flagging previous events

[ Edited ]

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.

Super User
Posts: 5,352

Re: flagging previous events

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 208 views
  • 1 like
  • 5 in conversation