DATA Step, Macro, Functions and more

problem with merge

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

problem with merge

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


Accepted Solutions
Solution
‎09-28-2014 07:23 AM
Super Contributor
Posts: 490

Re: problem with merge

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,799

Re: problem with merge

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

Contributor
Posts: 49

Re: problem with merge

Posted in reply to data_null__

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

Respected Advisor
Posts: 3,156

Re: problem with merge

What rules you use to get this outcome?

Super Contributor
Posts: 490

Re: problem with merge

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.

Contributor
Posts: 49

Re: problem with merge

Posted in reply to mohamed_zaki

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!

Respected Advisor
Posts: 3,156

Re: problem with merge

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;

Solution
‎09-28-2014 07:23 AM
Super Contributor
Posts: 490

Re: problem with merge

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 349 views
  • 3 likes
  • 4 in conversation