problem with merge

[Rakeon]

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

[data_null__]

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

[Rakeon]

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

[Haikuo]

What rules you use to get this outcome?

[mohamed_zaki]

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.

[Rakeon]

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!

[Haikuo]

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;

[mohamed_zaki · Accepted solution]

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