BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Gayatrikunchay
Obsidian | Level 7

Hi,

I have the following data with these variables. For the same ID, the race/ethnicity is different, when it is supposed to be the same.

 

ID_personAdmit DateRaceEthnicity
12341/03/2019Non-Hispanic Black
12342/06/2019Non-Hispanic White
34215/14/2020Hispanic
34213/21/2021Non-Hispanic White
45366/4/2020Unknown

 

To standardize the race/ethnicity information, I decided to make the race/ethnicity for each ID same as it was at the earliest admission date. How do I code for this?

I need the data to be like this:

 

ID_personAdmit DateRaceEthnicityRaceeth_new
12341/03/2019Non-Hispanic BlackNon-Hispanic Black
12342/06/2019Non-Hispanic WhiteNon-Hispanic Black
34215/14/2020HispanicHispanic
34213/21/2021Non-Hispanic WhiteHispanic
45366/4/2020UnknownUnknown
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

Assuming your data is sorted by ID_person and Admit_Date

 

data want;
    set have;
    by id_person;
    retain raceeth_new;
    if first.id_person then raceeth_new=racethnicity;
run;


Code is untested, as I cannot test the code against your screen captures (I can only test code against SAS data sets, please from now on provide SAS data sets as working SAS data step code, which you can type in yourself or follow these instructions)

--
Paige Miller

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

Assuming your data is sorted by ID_person and Admit_Date

 

data want;
    set have;
    by id_person;
    retain raceeth_new;
    if first.id_person then raceeth_new=racethnicity;
run;


Code is untested, as I cannot test the code against your screen captures (I can only test code against SAS data sets, please from now on provide SAS data sets as working SAS data step code, which you can type in yourself or follow these instructions)

--
Paige Miller
Gayatrikunchay
Obsidian | Level 7

Hi, this worked great! But what if the earliest admission date has 'Unknown" Race and Ethnicity? How do I code to take the race/ethnicity for next admission date? Thank you!

PaigeMiller
Diamond | Level 26

Delete the rows with missing RACEETHNICITY and then run my code.

--
Paige Miller
Gayatrikunchay
Obsidian | Level 7

I cannot delete the rows as I have other variables which has important information. Any other way?

PaigeMiller
Diamond | Level 26

@Gayatrikunchay wrote:

I cannot delete the rows as I have other variables which has important information. Any other way?


Delete the rows, run my code, obtain one row per ID, and then merge the results ReceEthnicity_new back into the original data set by ID, then no rows are missing.

--
Paige Miller
Gayatrikunchay
Obsidian | Level 7

This worked!! Thank you!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 887 views
  • 2 likes
  • 2 in conversation