topic Merging two files, and selecting certain data based off row data in New SAS User

Merging two files, and selecting certain data based off row data

Kbug — Tue, 16 Jul 2019 17:04:22 GMT

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

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

23453 inpatient 3/34/2323 M

23453 emergency 3/34/2323 M

34543 emergency 1/2/3456 F

54353 emergency 9/7/6543 M

Re: Merging two files, and selecting certain data based off row data

PaigeMiller — Tue, 16 Jul 2019 14:31:28 GMT

data want;
    merge file1 file2;
    by id;
    if admit_class='inpatient';
run;

It's not clear to me why FILE1 is needed at all.

Re: Merging two files, and selecting certain data based off row data

Kbug — Tue, 16 Jul 2019 14:41:12 GMT

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!

Re: Merging two files, and selecting certain data based off row data

PaigeMiller — Tue, 16 Jul 2019 14:45:20 GMT

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.

Re: Merging two files, and selecting certain data based off row data

Kbug — Tue, 16 Jul 2019 17:12:58 GMT

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

56789 inpatient 12/12/1212 M

98765 inpatient 0/0/0000 F

24689 inpatient 4/4/4444 M

13579 inpatient 6/5/6565 F

Re: Merging two files, and selecting certain data based off row data

PaigeMiller — Tue, 16 Jul 2019 17:14:42 GMT

data want;
    merge file1(in=in1) file2;
    by id;
    if in1 and admit_class='inpatient';
run;