Assigning IDs to observations based on two criteria

[advmsj]

I am trying to assign unique IDs to observations based on 2 criteria: dSEQ and DOB.

dSEQ	DOB	id
12344	31/03/1990	1
12344	31/03/1990	1
45822	31/03/1990	2
45823	25/01/1982	3
98615	01/01/1987	4
98615	01/01/1989	5
98615	01/01/1989	5

I have used this code so far but it is not accounting for DOB and I end up with the same ID going to the same dSEQ with different DOB...

proc sort data=HAVE;
by dSEQ DOB;
run;

* ID assignment;
data HAVE;
set WANT;
by dSEQ DOB;
retain id 0;
if first.dSEQ and first.DOB then id=id+1;
run;

[Shmuel]

Tested correct code is

data want;
 set have;
  by dSEQ DOB;
      retain id 0;
      if first.DOB then id=id+1;
run;

[advmsj]

Hi there, unfortunately this code is giving me the wrong output:

ID	dSEQ	DOB
250	****283058392	17-Oct-10
250	****598659854	17-Oct-10

The same ID is being given to different dSEQ

[Shmuel]

Adapt your sample data to include those observations.

Use a datastep with cards/datalines to create the HAVE dataset.

[ballardw]

---

@advmsj wrote:

Hi there, unfortunately this code is giving me the wrong output:

ID	dSEQ	DOB
250	****283058392	17-Oct-10
250	****598659854	17-Oct-10

The same ID is being given to different dSEQ

---

Show your exact code and provide example data that duplicates this claimed behavior.

[novinosrin · Accepted solution]

HI @advmsj  Please try

data want;
 set have;
 by dseq dob;
 if first.dob then id+1;
run;

[advmsj]

Hi there, I am going to test this out and will let you know if it works.

[ballardw]

---

@advmsj wrote:

Hi there, unfortunately this didn't work

---

SHOW YOUR CODE AND DATA.

Best is to copy your submitted code from the LOG with all the messages and paste it into a code box opened on the forum with the </>. That way we see what actual code was run.

Claiming something "didn't work" without showing exactly what you actually did does not lead us any closer to a solution.

[advmsj]

The data I am using contains sensitive information. Please hold on while I try to recreate it with different DOBs....

[advmsj]

data SASIDTest;
input dSEQ DOB: mmddyy8.;
format DOB mmddyy8.;
datalines;
283058392	09/01/2015
283058392	09/01/2016
359852186	04/24/2011
598659875	05/29/2012
598659875	05/29/2012
442115847	01/17/2011
598632147	05/01/2010
698578958	10/30/2008
326597421	12/21/2001
698754896	10/20/2000
;
run;

proc sort data=SASIDTest;
by dSEQ DOB;
run;

* ID assignment;
data SASIDTest1;
set SASIDTest;
by dSEQ DOB;
retain id 0;
if first.dSEQ and first.DOB then id=id+1;
run;

proc print data=SASIDTest1;
var id dSEQ DOB;
run;

The first 2 observations should have different IDs

[novinosrin]

data SASIDTest;
input dSEQ DOB: mmddyy8.;
format DOB mmddyy8.;
datalines;
283058392	09/01/2015
283058392	09/01/2016
359852186	04/24/2011
598659875	05/29/2012
598659875	05/29/2012
442115847	01/17/2011
598632147	05/01/2010
698578958	10/30/2008
326597421	12/21/2001
698754896	10/20/2000
;
run;

proc sort data=SASIDTest;
by dSEQ DOB;
run;


data want;
 set SASIDTest;
 by dseq dob;
 if first.dob then id+1;
run;

dSEQ	DOB	id
283058392	09/01/15	1
283058392	09/01/16	2
326597421	12/21/01	3
359852186	04/24/11	4
442115847	01/17/11	5
598632147	05/01/10	6
598659875	05/29/12	7
598659875	05/29/12	7
698578958	10/30/08	8
698754896	10/20/00	9

[advmsj]

Thank you for your patience. I am a new SAS user and imported my data from Excel. I was having a really hard time recreating the data using the input and datalines statements (I could not get the date format to work)...

I realized I still had id=id+1 which was why it was not working.

Thank you again for your patience and constant help.

[novinosrin]

No worries. Take care, stay safe and Have fun!