BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
seltonsy
Quartz | Level 8

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,

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
art297
Opal | Level 21

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

 

seltonsy
Quartz | Level 8

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

art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 7418 views
  • 2 likes
  • 2 in conversation