BookmarkSubscribeRSS Feed
sasworker16
Calcite | Level 5

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.

4 REPLIES 4
atul_desh
Quartz | Level 8

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
Quartz | Level 8

For #1

 

 

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

Reeza
Super User

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
Super User Tom
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 990 views
  • 0 likes
  • 4 in conversation