Hello,
I am trying to
#1. Merging Two Data Sets that contains common and unique values for each data set into one single data set.
-------------------------------------------------------------------------------------------------------------------------------------------------------
data passenger_group1;
input ID;
cards;
1001
1002
1003
1006
1007
run;
data passenger_group2;
inputID;
cards;
1001
1002
1003
1004
1005
run;
data passenger_group1and2;
set passenger_group1 passenger_group2;
run;
proc sort data=passenger_group1and2 out=passenger_want nodupkey; by ID; run;
--------------------------------------------------------------------------------------------------------------------------------------------------------
-> This seems to be working, but is there an efficient way to do this by using PROC SQL?
#2. Merging and Matching Two Data Sets
-----------------------------------------------------------------------
data passenger_group1;
input ID Class$;
cards;
1001 A
1002 A
1003 B
1006 C
1007 C
run;
data annual_fee;
input Class$ Annual_Fee;
cards;
A 800
B 600
C 400
run;
------------------------------------------------------------------------
How can I match and merge these two data sets so that I can get the result like the following?
---------------------------------------------
ID Class Annual_Fee
1001 A 800
1002 A 800
1003 B 600
1006 C 400
1007 C 400
-------------------------------------------
Any help or advice would be really appreciated.
Thank You.
For # 2
proc sql;
create table want as select p.*, a.Annual_Fee from passenger_group1 p, annual_fee a where
a.class=p.class;
quit;
For #1
proc sql;
create table combined as
select * from passenger_group1 a, passenger_group2 b where a.ID=b.ID;
quit;
1. Union in SQL, the Venn diagrams are very helpful here.
2. You have many options - this is generally called a look up. You can do a SQL join, proc format or a data step merge, or a hash table.
Your first example doesn't look to be a "merge". Instead you are concatenating (or in SQL speak UNIONing) the tables.
* Data step, assuming data is already sorted ;
data both;
set group1 group2;
by id;
run;
* SQL code ;
proc sql ;
create table both as
select * from group1
union
select * from group2
;
quit;
The second example looks like a merge. The SAS code is easier than the SQL code, but you need pre-sort the data.
* Data step, assuming data is already sorted ;
data both;
merge group1 fees;
by class;
run;
* SQL code ;
proc sql ;
create table both as
select a.id
, coalesce(a.class,b.class) as class
, b.Annual_Fee
from group1 a
full join fees b
on a.class = b.class
;
quit;
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!
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.