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;
HI @advmsj Please try
data want;
set have;
by dseq dob;
if first.dob then id+1;
run;
Tested correct code is
data want;
set have;
by dSEQ DOB;
retain id 0;
if first.DOB then id=id+1;
run;
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
Adapt your sample data to include those observations.
Use a datastep with cards/datalines to create the HAVE dataset.
@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.
HI @advmsj Please try
data want;
set have;
by dseq dob;
if first.dob then id+1;
run;
Hi there, I am going to test this out and will let you know if it works.
@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.
The data I am using contains sensitive information. Please hold on while I try to recreate it with different DOBs....
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
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 |
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.
No worries. Take care, stay safe and Have fun!
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.