Hi...I have a dataset that I want to select only or identify those customers as repaet customers by their ID. I can obtain a partial dataset by using the lag function but the very first record of each customer who is a repeat customer does not have "Yes" as the correct entry. Any suggestions how I might be able include the first time that repeat customer ID is found...Thanks.
Have:
ID | DATE | SALES |
1223 | 2016-01-23 | 73.55 |
1445 | 2016-01-12 | 88.45 |
1445 | 2016-02-11 | 54.70 |
1445 | 2016-03-10 | 66.15 |
1733 | 2016-02-08 | 34.50 |
1649 | 2016-02-27 | 66.30 |
1649 | 2016-03-13 | 88.20 |
Want:
ID | Date | Sales | Repeat |
1223 | 2016-01-23 | 73.55 | |
1445 | 2016-01-12 | 88.45 | Yes |
1445 | 2016-02-11 | 54.70 | Yes |
1445 | 2016-03-10 | 66.15 | Yes |
1733 | 2016-02-08 | 34.50 | |
1649 | 2016-02-27 | 66.30 | Yes |
1649 | 2016-03-13 | 88.20 | Yes |
Use the By groups?
data want;
set have;
BY ID;
if not first.ID then repeat='Yes';
run;
EDIT: Sorry, it looks like you want to identify all records that aren't unique. You can do this using First/Last, where not first.ID and last.iD
data want;
set have;
BY ID;
if not (first.ID and last.ID) then repeat='Yes';
run;
Use the By groups?
data want;
set have;
BY ID;
if not first.ID then repeat='Yes';
run;
EDIT: Sorry, it looks like you want to identify all records that aren't unique. You can do this using First/Last, where not first.ID and last.iD
data want;
set have;
BY ID;
if not (first.ID and last.ID) then repeat='Yes';
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.