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!
Awesome. That worked perfectly. I wasn't aware of the notsorted piece but that is exactly what I needed.
Thank you so much!
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.
Awesome. That worked perfectly. I wasn't aware of the notsorted piece but that is exactly what I needed.
Thank you so much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.