topic Re: Assigning IDs to observations based on two criteria in SAS Procedures

Assigning IDs to observations based on two criteria

advmsj — Thu, 20 Aug 2020 17:51:29 GMT

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;

Re: Assigning IDs to observations based on two criteria

Shmuel — Thu, 20 Aug 2020 18:17:43 GMT

Tested correct code is

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

Re: Assigning IDs to observations based on two criteria

advmsj — Thu, 20 Aug 2020 18:26:06 GMT

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

Re: Assigning IDs to observations based on two criteria

Shmuel — Thu, 20 Aug 2020 19:02:40 GMT

Adapt your sample data to include those observations.

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

Re: Assigning IDs to observations based on two criteria

novinosrin — Thu, 20 Aug 2020 19:11:46 GMT

HI @advmsj Please try

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

Re: Assigning IDs to observations based on two criteria

ballardw — Thu, 20 Aug 2020 19:25:27 GMT

@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.

Re: Assigning IDs to observations based on two criteria

advmsj — Thu, 20 Aug 2020 19:51:42 GMT

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

Re: Assigning IDs to observations based on two criteria

ballardw — Thu, 20 Aug 2020 19:29:17 GMT

@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.

Re: Assigning IDs to observations based on two criteria

advmsj — Thu, 20 Aug 2020 19:31:59 GMT

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

Re: Assigning IDs to observations based on two criteria

advmsj — Thu, 20 Aug 2020 19:46:11 GMT

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

Re: Assigning IDs to observations based on two criteria

novinosrin — Thu, 20 Aug 2020 19:53:25 GMT


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

Re: Assigning IDs to observations based on two criteria

advmsj — Thu, 20 Aug 2020 20:04:35 GMT

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.

Re: Assigning IDs to observations based on two criteria

novinosrin — Thu, 20 Aug 2020 20:07:03 GMT

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