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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.