Hello,
I am currently using SAS 9.4 and am trying to merge two sets of data. Both of these data's have ID numbers that I want to merge by, so I have already sorted by that variable. One of the files only contain the ID numbers(file1) and the other file contains demographics and their admit class: inpatient, emergency, etc. And also contains other variables, such as DOB, and gender. (file 2). Some of the ID numbers in the demographics have multiple inpatients or emergencies.
What I am looking to do is merge both of these files by their ID name, and only take all the rows from file 2 that contain 'inpatient' for the admit class variable and ignore the emergency rows. How would I go about doing this after I have sorted both files? Below is an example.
File 1:
ID number
12345
56789
98765
54321
24689
13579
File 2:
ID number admit class DOB gender
12345 emergency 1/1/2019 M
12345 inpatient 1/1/2019 M
12345 inpatient 1/1/2019 M
56789 inpatient 12/12/1212 M
98765 emergency 0/0/0000 F
98765 inpatient 0/0/0000 F
54321 emergency 20/20/2020 F
24689 inpatient 4/4/4444 M
13579 inpatient 6/5/6565 F
13579 inpatient 6/5/6565 F
23453 inpatient 3/34/2323 M
23453 emergency 3/34/2323 M
34543 emergency 1/2/3456 F
54353 emergency 9/7/6543 M
data want;
merge file1(in=in1) file2;
by id;
if in1 and admit_class='inpatient';
run;
data want;
merge file1 file2;
by id;
if admit_class='inpatient';
run;
It's not clear to me why FILE1 is needed at all.
Sorry, I realized I didn't add any other data that didn't match up with file 1 and didn't add any extra rows as well, I have now edited it!
It's still not clear to me what role FILE1 plays here.
It's not clear to me how ID 12345 can have two different birth dates and two different genders.
Sorry about the error with the two different birthdays and genders, I was trying to quickly edit it and made a mistake, it has been fixed.
The purpose of file 1 is to use those ID numbers and get the information that correlates with the same ID number from file 2. File 2 has so many other ID numbers that I do not need when I merge the files, and I only want the information for the ID numbers that are in file 1. So by the end I want it to be like this:
Merged files:
ID Number admit class DOB Gender
12345 inpatient 1/1/2019 M
12345 inpatient 1/1/2019 M
56789 inpatient 12/12/1212 M
98765 inpatient 0/0/0000 F
24689 inpatient 4/4/4444 M
13579 inpatient 6/5/6565 F
13579 inpatient 6/5/6565 F
data want;
merge file1(in=in1) file2;
by id;
if in1 and admit_class='inpatient';
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.