Simple Question About Merging and Matching Data in SAS

[sasworker16]

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.

[atul_desh]

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;

[atul_desh]

For #1

proc sql;
create table combined as
select * from passenger_group1 a, passenger_group2 b where a.ID=b.ID;
quit;

[Reeza]

1. Union in SQL, the Venn diagrams are very helpful here. 

http://support.sas.com/documentation/cdl/en/sqlproc/69049/HTML/default/viewer.htm#n0vo2lglyrnexwn14e...

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. 

http://support.sas.com/documentation/cdl/en/sqlproc/69049/HTML/default/viewer.htm#p0o4a5ac71mcchn1kc...

[Tom]

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;