topic First. with by Groups in SAS Programming

First. with by Groups

laholden — Mon, 26 Jun 2017 13:37:11 GMT

Hi, I have a dataset with customer id, date, and segment. It is sorted by customer id and date because I want to utilize first. to figure out the first date that a customer entered a new segment.

A customer can re-enter a segment multiple times and stay there for a few weeks. However, I do not want it to continue counting weeks when they re-enter a segment. I want the counter to start over.

Data Set currently looks like this:

Customer ID	Date	Segment
100100	1-Nov-15	Analog
100100	8-Nov-15	Analog
100100	15-Nov-15	Analog
100100	22-Nov-15	Digital
100100	29-Nov-15	Digital
100100	6-Dec-15	Analog
100100	13-Dec-15	Analog

Desired Output looks like this:

Customer ID	Date	Segment	Segment Date
100100	1-Nov-15	Analog	1
100100	8-Nov-15	Analog	2
100100	15-Nov-15	Analog	3
100100	22-Nov-15	Digital	1
100100	29-Nov-15	Digital	2
100100	6-Dec-15	Analog	1
100100	13-Dec-15	Analog	2

I can get the counter and re-set to work with the code below, but it does not start over from 1 when a customer re-enters a previous segment. Instead, it continues increasing the counter. My code and current output is below:

data lastdig0;
set lastdig0(drop=segment_weeks);
segment_weeks+1;
by tid segment;
if first.tid or first.segment then segment_weeks=1;
run;

Customer ID	Date	Segment	Segment Date
100100	22-Nov-15	Digital	1
100100	29-Nov-15	Digital	2
100100	1-Nov-15	Analog	1
100100	8-Nov-15	Analog	2
100100	15-Nov-15	Analog	3
100100	6-Dec-15	Analog	4
100100	13-Dec-15	Analog	5

Any ideas on how to get this to correctly?

Thank you!

Re: First. with by Groups

Kurt_Bremser — Mon, 26 Jun 2017 13:56:59 GMT

Try this:

data have;
infile cards dlm='09'x;
input customerid $ date :date9. segment $;
format date date9.;
cards;
100100	1-Nov-15	Analog
100100	8-Nov-15	Analog
100100	15-Nov-15	Analog
100100	22-Nov-15	Digital
100100	29-Nov-15	Digital 
100100	6-Dec-15	Analog
100100	13-Dec-15	Analog
;
run;

data want;
set have;
by customerid segment notsorted;
if first.customerid or first.segment
then segment_date = 1;
else segment_date + 1;
run;

proc print data=want noobs;
run;

Result:

                                      segment_
customerid         date    segment      date

  100100      01NOV2015    Analog         1   
  100100      08NOV2015    Analog         2   
  100100      15NOV2015    Analog         3   
  100100      22NOV2015    Digital        1   
  100100      29NOV2015    Digital        2   
  100100      06DEC2015    Analog         1   
  100100      13DEC2015    Analog         2

Note how I put your data in a data step and used the "little running man" icon to post the code. Makes recreating your situation with a simple copy/paste that much easier.

Re: First. with by Groups

laholden — Mon, 26 Jun 2017 14:05:52 GMT

Awesome. That worked perfectly. I wasn't aware of the notsorted piece but that is exactly what I needed.

Thank you so much!