DATA Step, Macro, Functions and more

re: Data Selection

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

re: Data Selection

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

Accepted Solutions
Solution
‎05-11-2016 06:33 PM
Super User
Posts: 17,784

Re: re: Data Selection

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎05-11-2016 06:33 PM
Super User
Posts: 17,784

Re: re: Data Selection

[ Edited ]

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;
☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 157 views
  • 0 likes
  • 2 in conversation