Defining sequence variable for duplications

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Defining sequence variable for duplications

Morning guys!

I have a variable (column) named ID and there are some duplicates therein. I want to add a new column showing the sequence of duplicates in ID. For example if we have three values of 10 in a line for ID consecutively, there should be 1, 2, and 3 for the new SEQ column respectively. Here is an example of what I'd like to do:


ID
Seq
11
21
22
23
31
32
41
51
52
53
54
61

Any comments would be highly appreciated!

Issac


Accepted Solutions
Solution
‎10-17-2012 09:06 AM
PROC Star
Posts: 7,363

Re: Defining sequence variable for duplications

Is your data already in id order?  If not, you either have to use a hash or first presort it by id.  e.g.:

proc sort data=have out=want;

by id;

run;

Then you could achieve what you want in a simple datastep.  e.g.:

data want;

  set want;

  by id;

  if first.id then seq=1;

  else seq+1;

run;

View solution in original post


All Replies
Solution
‎10-17-2012 09:06 AM
PROC Star
Posts: 7,363

Re: Defining sequence variable for duplications

Is your data already in id order?  If not, you either have to use a hash or first presort it by id.  e.g.:

proc sort data=have out=want;

by id;

run;

Then you could achieve what you want in a simple datastep.  e.g.:

data want;

  set want;

  by id;

  if first.id then seq=1;

  else seq+1;

run;

Contributor
Posts: 65

Re: Defining sequence variable for duplications

Thanks so much Arthur!

BTW, would you recommend me a reference book that has such examples worked out with help of SAS functions?

PROC Star
Posts: 7,363

Re: Defining sequence variable for duplications

Ron Cody's book provides a nice intro.  Here is a link to the book's examples:

http://ftp.sas.com/samples/A59343

However, there are so many functions, I have always found forums like this one and SAS-L ( Archives of SAS-L@LISTSERV.UGA.EDU</title><style type="text/css"><!--BODY { font-family: "Comic Sans...  ) the best way to discover what function might be needed for any specific purpose.

Contributor
Posts: 65

Re: Defining sequence variable for duplications

Thanks very much!

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 241 views
  • 0 likes
  • 2 in conversation