topic Re: flagging previous events in SAS Programming

flagging previous events

darb — Mon, 26 Jun 2017 15:31:13 GMT

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!

Re: flagging previous events

Astounding — Mon, 26 Jun 2017 15:49:14 GMT

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;

Re: flagging previous events

art297 — Mon, 26 Jun 2017 15:49:18 GMT

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

Re: flagging previous events

darb — Mon, 26 Jun 2017 16:14:49 GMT

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

Re: flagging previous events

jdwaterman91 — Mon, 26 Jun 2017 16:15:20 GMT

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;

Re: flagging previous events

darb — Mon, 26 Jun 2017 16:16:53 GMT

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

Re: flagging previous events

darb — Mon, 26 Jun 2017 16:20:56 GMT

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.

Re: flagging previous events

Astounding — Mon, 26 Jun 2017 16:22:39 GMT

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.

Re: flagging previous events

jdwaterman91 — Mon, 26 Jun 2017 16:27:17 GMT

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.

Re: flagging previous events

darb — Mon, 26 Jun 2017 16:28:30 GMT

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.

Re: flagging previous events

darb — Mon, 26 Jun 2017 16:35:57 GMT

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

Re: flagging previous events

Astounding — Mon, 26 Jun 2017 18:45:10 GMT

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;

Re: flagging previous events

art297 — Mon, 26 Jun 2017 20:20:10 GMT

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.

Re: flagging previous events

jdwaterman91 — Mon, 26 Jun 2017 18:39:53 GMT

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;

Re: flagging previous events

darb — Mon, 26 Jun 2017 19:10:23 GMT

thank you! all good 🙂

Re: flagging previous events

Astounding — Mon, 26 Jun 2017 19:23:57 GMT

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;

run;

Re: flagging previous events

art297 — Mon, 26 Jun 2017 20:17:59 GMT

@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

Re: flagging previous events

Ksharp — Tue, 27 Jun 2017 12:46:53 GMT

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;