topic Re: problem with merge in SAS Programming

problem with merge

Rakeon — Sun, 28 Sep 2014 09:41:45 GMT

Hi,

I have a problem with left join inside the SAS merge:

I have two table(I can't give you the real tables or the log files, becouse the policy of the enterprise where I'm working is very strictly 😞

table A:

id source _a _b

1 a re 123

2 b ba 53

3 a ag 679

3 b 45 78

table B

id number

1 13

2 14

3 2

If I will make a left join with this SAS code:

data C;

merge a(in=a)

b(in=b);

by id;

if a;

if source = 'a' then number = . ;

run;

the data set has both of row 3 with the missing value, instead of just one(with the source = a);

is there a logical mystake in the sas code(merge)? i

Re: problem with merge

data_null__ — Sun, 28 Sep 2014 10:23:29 GMT

SAS and MERGE are working properly. What result do you seek?

Re: problem with merge

mohamed_zaki — Sun, 28 Sep 2014 11:23:47 GMT

You should be aware of :

That different problems may arises when MERGE, IF-THEN statement used within one data step.

Kindly check:

Be Careful When You Merge SAS Datasets!

So use two data step instead of one, as one work around for your case.

mohamed zaki

Re: problem with merge

Rakeon — Sun, 28 Sep 2014 11:34:11 GMT

hi data _null_,

I would like to have ths result :

TABLE C:

id source _a _b number

1 a re 123 13

2 b ba 53 14

3 a ag 679 .

3 b 45 78 2

Re: problem with merge

Haikuo — Sun, 28 Sep 2014 16:54:46 GMT

What rules you use to get this outcome?

Re: problem with merge

mohamed_zaki — Sun, 28 Sep 2014 19:34:22 GMT

i think his last post contain typo

it should be

TABLE C:

id source _a _b number

1 a re 123 .

2 b ba 53 14

3 a ag 679 .

3 b 45 78 2

as based on the original post.

Re: problem with merge

Haikuo — Sun, 28 Sep 2014 19:55:19 GMT

if @mohammed.zaki read your mind correctly, then a little tweak in your code is needed:

data C;

merge a(in=a)

b(in=b rename=number=num);

by id;

if a;

if source = 'a' then

number = .;

else number=num;

drop num;

run;

Re: problem with merge

Rakeon — Fri, 03 Oct 2014 20:09:23 GMT

Hi ,

sorry for my delay in my answer....

I yes the result is what that zaki said...

I used the code of hai.kuo and it works.

thanks!