Help using Base SAS procedures

Help with simple (I thought) merge statement

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Help with simple (I thought) merge statement

I am trying to merge together two SAS datasets.  I have a simple example below.  When I merge the two datasets together by the ZIP I am expecting 14 unique records....SAS is only returning 7.  I have included the datasets, the actual output, the output I was expecting and the code to merge the datasets together.  Can anyone tell me what I'm doing wrong?

FOURHOUR DATASET  

Obs    ZIP     BOSHIP

1     40219     02TK

2     40219     02TJ

3     40219     02TM

4     40219     02TV

5     40219     02TU

6     40219     02TR

7     40219     02TE

ORDER DATASET

                    ORDER_

Obs    ZIP     NUMBER

1     40219    B401632K

2     40219    R765534K

MERGE STATEMENT:


PROC SORT DATA=FOURHOUR;

BY ZIP;

RUN;

PROC SORT DATA=ORDER;

BY ZIP;

RUN;

DATA FOURHOUR;

MERGE ORDER(IN=A) FOURHOUR(IN=B);

BY ZIP;

IF A AND B;

OUTPUT:

                    ORDER_

Obs    ZIP     NUMBER      BOSHIP

1     40219    B401632K     02TK

2     40219    R765534K     02TJ

3     40219    R765534K     02TM

4     40219    R765534K     02TV

5     40219    R765534K     02TU

6     40219    R765534K     02TR

7     40219    R765534K     02TE

OUTPUT I EXPECTED (AND NEED)

                    ORDER_

Obs    ZIP     NUMBER      BOSHIP

1     40219    B401632K     02TK

2     40219    B401632K     02TJ

3     40219    B401632K     02TM

4     40219    B401632K     02TV

5     40219    B401632K     02TU

6     40219    B401632K     02TR

7     40219    B401632K     02TE

8     40219     R765534K     02TK

9     40219     R765534K     02TJ

10    40219     R765534K     02TM

11    40219     R765534K     02TV

12     40219    R765534K     02TU

13     40219    R765534K     02TR

14     40219    R765534K     02TE


Accepted Solutions
Solution
‎07-06-2011 04:09 PM
Frequent Contributor
Posts: 104

Help with simple (I thought) merge statement

SAS's data step merge when there are duplicate keys in both datasets are decidedly unconventional, to say the least.  It is not the SQL equivalent of a cartesian product, which is what you are looking for.  Try something like below to get what you want:

proc sql;

     create table c as (

          select * from a,b

          where a.key = b.key

     );

quit;

In your case, it's "select * from fourhour, order where fourhour.zip = order.zip".

View solution in original post


All Replies
Contributor QLi
Contributor
Posts: 57

Re: Help with simple (I thought) merge statement

I think Proc Sql maybe easy to do it.

Try this code.

data a;

input

n zip boship $ ;

datalines

;

1 40219 02TK

2 40219 02TJ

3 40219 02TM

4 40219 02TV

5 40219 02TU

6 40219 02TR

7 40219 02TE

;

 

data b;

input

n zip order_num $ ;

datalines

;

1 40219 B401632K

2 40219 R765534K

;

run;

porc sql;

select a.*, order_num

from a as a, b;

;

Quit;

Solution
‎07-06-2011 04:09 PM
Frequent Contributor
Posts: 104

Help with simple (I thought) merge statement

SAS's data step merge when there are duplicate keys in both datasets are decidedly unconventional, to say the least.  It is not the SQL equivalent of a cartesian product, which is what you are looking for.  Try something like below to get what you want:

proc sql;

     create table c as (

          select * from a,b

          where a.key = b.key

     );

quit;

In your case, it's "select * from fourhour, order where fourhour.zip = order.zip".

New Contributor
Posts: 2

Help with simple (I thought) merge statement

Thanks to both of the above posts regarding the Proc SQL suggestion!.  The following worked.

PROC Sql;

CREATE TABLE ALLMERGE AS

SELECT * FROM

ORDER A,FOURHOUR B

WHERE A.ZIP=B.ZIP;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 124 views
  • 3 likes
  • 3 in conversation