DATA Step, Macro, Functions and more

Create ID variable for repeated rows

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Create ID variable for repeated rows

Hi,

 

I'd like to create a new variable (ID, an numerical variable from 1 to x) from a dataset that has repeated rows per medical file number. The medical file numbers are not consequent, I'd like to create a new variable with consequent ID nos for every medical file number. I show below an example, I want to create the last column (1,2,3.....)

 

example

 

file1867678  ID1    1

file1867678  ID1    1

file1867678  ID1    1

file29898998  ID2  2

file29898998  ID2  2

file34343333  ID3  3

file34343333  ID3  3

file34343333  ID3  3

 

Thanks,


Accepted Solutions
Solution
‎03-29-2017 09:12 AM
PROC Star
Posts: 7,360

Re: Create ID variable for repeated rows

[ Edited ]

As long as all same id records are adjacent to each other:

 

data want;
  set have;
  by filenum notsorted;
  if first.filenum then id+1;
run

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
‎03-29-2017 09:12 AM
PROC Star
Posts: 7,360

Re: Create ID variable for repeated rows

[ Edited ]

As long as all same id records are adjacent to each other:

 

data want;
  set have;
  by filenum notsorted;
  if first.filenum then id+1;
run

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 40

Re: Create ID variable for repeated rows

Thanks for the effective solution.
I have another question, maybe you can help as well.

Some of the filenumbers are repeated (because they belong to the same patient) but they come from different admissions to the hospital (it is seen from the other variables). Can I get an ID for admission and not patient. I'm thinking I can use a newly recorded measure in another variable as well in the code that you sent me to identify a new entry (i.e. admission).
ex.
file1867678 ID1 1     m1   NEW_ID1
file1867678 ID1 1             NEW_ID1
file1867678 ID1 1             NEW_ID1
file1867678 ID1 1     m2   NEW_ID2
file1867678 ID1 1             NEW_ID2
file29898998 ID2 2   m1   NEW_ID3
file29898998 ID2 2           NEW_ID3
file34343333 ID3 3   m1   NEW_ID4
file34343333 ID3 3           NEW_ID4
file34343333 ID3 3           NEW_ID4
file34343333 ID3 3   m2   NEW_ID5
file34343333 ID3 3           NEW_ID5
file34343333 ID3 3           NEW_ID5

 

Thanks

PROC Star
Posts: 7,360

Re: Create ID variable for repeated rows

You didn't say what your variables were called, so I called the one with the m1 m2 values .. adm

 

The following does what you want:

 

data want;
  set have;
  by filenum adm notsorted;
  if first.adm and not missing(adm) then new_id+1;
run;

Art, CEO, AnalystFinder.com

 

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 139 views
  • 1 like
  • 2 in conversation