First. with by Groups

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

First. with by Groups

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 IDDateSegment
1001001-Nov-15Analog
1001008-Nov-15Analog
10010015-Nov-15Analog
10010022-Nov-15Digital
10010029-Nov-15Digital 
1001006-Dec-15Analog
10010013-Dec-15Analog

 

Desired Output looks like this: 

Customer IDDateSegmentSegment Date
1001001-Nov-15Analog1
1001008-Nov-15Analog2
10010015-Nov-15Analog3
10010022-Nov-15Digital1
10010029-Nov-15Digital 2
1001006-Dec-15Analog1
10010013-Dec-15Analog2

 

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 IDDateSegmentSegment Date
10010022-Nov-15Digital1
10010029-Nov-15Digital 2
1001001-Nov-15Analog1
1001008-Nov-15Analog2
10010015-Nov-15Analog3
1001006-Dec-15Analog4
10010013-Dec-15Analog5

 

Any ideas on how to get this to correctly?

 

Thank you!


Accepted Solutions
Solution
‎07-11-2017 02:07 PM
New Contributor
Posts: 2

Re: First. with by Groups

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

 

Thank you so much! 

 

 

View solution in original post


All Replies
Super User
Posts: 7,472

Re: First. with by Groups

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎07-11-2017 02:07 PM
New Contributor
Posts: 2

Re: First. with by Groups

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

 

Thank you so much! 

 

 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 108 views
  • 1 like
  • 2 in conversation