topic Simple Question About Merging and Matching Data in SAS in SAS Programming

Simple Question About Merging and Matching Data in SAS

sasworker16 — Sun, 17 Jul 2016 03:56:02 GMT

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.

Re: Please Help: Simple Question About Merging and Matching Data in SAS

atul_desh — Sun, 17 Jul 2016 03:38:26 GMT

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;

Re: Simple Question About Merging and Matching Data in SAS

atul_desh — Sun, 17 Jul 2016 04:02:14 GMT

For #1

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

Re: Simple Question About Merging and Matching Data in SAS

Reeza — Sun, 17 Jul 2016 06:54:49 GMT

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

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

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#p0o4a5ac71mcchn1kc1zhxdnm139.htm

Re: Simple Question About Merging and Matching Data in SAS

Tom — Sun, 17 Jul 2016 16:43:26 GMT

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;