topic Help with simple (I thought) merge statement in SAS Procedures

Help with simple (I thought) merge statement

jvmiller — Wed, 06 Jul 2011 19:07:19 GMT

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

Re: Help with simple (I thought) merge statement

QLi — Wed, 06 Jul 2011 19:29:41 GMT

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;

Help with simple (I thought) merge statement

DLing — Wed, 06 Jul 2011 20:09:41 GMT

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".

Help with simple (I thought) merge statement

jvmiller — Wed, 06 Jul 2011 20:28:51 GMT

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;