topic Re: Create ID variable for repeated rows in SAS Programming

Create ID variable for repeated rows

seltonsy — Tue, 28 Mar 2017 20:53:35 GMT

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

file29898998 ID2 2

file34343333 ID3 3

Thanks,

Re: Create ID variable for repeated rows

art297 — Tue, 28 Mar 2017 21:06:14 GMT

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

Re: Create ID variable for repeated rows

seltonsy — Wed, 29 Mar 2017 13:55:49 GMT

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

Re: Create ID variable for repeated rows

art297 — Wed, 29 Mar 2017 14:09:55 GMT

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