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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.