Hello I have 3 datasets and as you can see, Ids from one table compared to another is not always the case.
I need to merge all 3 datasets by id and if variable(s) don't exist then they need to be blank:
Dataset1:
ID | age | gender |
12 | 15 | F |
23 | 18 | F |
33 | 16 | M |
42 | 14 | F |
Dataset2:
ID | SchoolYear | City | State |
12 | Freshmen | Brookline | MA |
42 | Freshmen | Brookline | MA |
46 | Senior | Framingham | MA |
48 | Senior | Quincy | MA |
Dataset3
ID | MathGrade | EnglishGrade |
23 | 75 | 65 |
46 | 81 | 99 |
48 | 95 | 65 |
Want
ID | age | gender | SchoolYear | City | State | MathGrade | EnglishGrade |
12 | 15 | F | Freshmen | Brookline | MA | ||
23 | 17 | F | 75 | 65 | |||
33 | 16 | M | |||||
42 | 14 | F | Freshmen | Brookline | MA | ||
46 | Senior | Framingham | MA | 81 | 99 | ||
48 | Senior | Quincy | MA | 95 | 65 |
What i have done so far is the following:
proc sql;
create table uniqueID as
select distinct ID from dataset1 union
select distinct ID from dataset2 union
select distinct ID from dataset3 ;
quit;
then
proc sql;
select a.*, b.*, c.*
from uniqueID a
left join dataset1 b on a.ID = b.ID
left join dataset2 b on a.ID = b.ID
left join dataset3 c on a.ID = c.ID;
quit;
not geting what i look for.
not geting what i look for.
are there errors in the log?
Please provide data in a usable form, following these instructions. Screen captures are not usable.
It looks like your data sets:
If that's the case, the solution is simple:
data want;
merge dataset1 dataset2 dataset3;
by id;
run;
If you might have multiple records for the same ID, we need to know more about what the final result should look like.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.