topic sas merge in SAS Programming

sas merge

nirupama1 — Thu, 22 Nov 2018 03:59:42 GMT

data ex1;
input ID Score;
cards;
1 25
2 28
3 35
4 45
;
run;

data ex2;
input ID Score;
cards;
1 95
2 97
;
run;

data comb;
merge ex1 ex2;
by ID;
run;

I read that if there are common variables in both datasets,the value is overwitten by the value in the right dataset.

So, I expect output as

1 95

2 97

3 35

4 45

But I am getting output as

1 25

2 28

3 35

4 45

Can someone explain why?

Thanks,

Nirupama

Re: sas merge

novinosrin — Thu, 22 Nov 2018 04:11:51 GMT

The below is what i got when i ran your code

ID=1 Score=95

ID=2 Score=97

ID=3 Score=35

ID=4 Score=45

Re: sas merge

Reeza — Thu, 22 Nov 2018 04:17:08 GMT

It does work as intended, so here's a couple of possibilities:

1. You're looking at the wrong data set - ex1 not comb

2. Code didn't run and this is an old data set - check your log

3. Your data isn't as shown for some reason or the code was mistyped.

Either way, if you can't figure it out I suggest posting the log from your run.

@nirupama1 wrote:

data ex1;
input ID Score;
cards;
1 25
2 28
3 35
4 45
;
run;

data ex2;
input ID Score;
cards;
1 95
2 97
;
run;

data comb;
merge ex1 ex2;
by ID;
run;

I read that if there are common variables in both datasets,the value is overwitten by the value in the right dataset.

So, I expect output as

1 95

2 97

3 35

4 45

But I am getting output as

1 25

2 28

3 35

4 45

Can someone explain why?

Thanks,

Nirupama

Re: sas merge

singhsahab — Thu, 22 Nov 2018 07:09:18 GMT

Hi Nirupama,

as per your code we are getting output as you expected .

Score

If you wanted to stop overwrite , you should use proc SQL like below.

proc SQL;
select coalesce(a.id,b.id) as id , coalesce(a.Score,b.Score) as score from ex1 as a left join ex2 as b on
a.id = b.id;
quit;

This will return output as :

id score

1	25
2	28
3	35
4	45

Thanks,

Re: sas merge

novinosrin — Thu, 22 Nov 2018 15:41:39 GMT

While I'm not so creative like you are to offer so fancy alternatives, I;m not sure the creativitiy requires heavy usage of coalesce and beyond assignments unless there are missing score values but not wanting to overwrite in itself is defeated by coalesce even though it's missing as missing can be valid. So,. It can be as simple as just select a.*


proc SQL;
create table want as 
select a.* 
from ex1 as a left join ex2 as b 
on a.id = b.id;
quit;

@singhsahab wrote:

If you wanted to stop overwrite , you should use proc SQL like below.

proc SQL;
select coalesce(a.id,b.id) as id , coalesce(a.Score,b.Score) as score from ex1 as a left join ex2 as b on
a.id = b.id;
quit;

This will return output as :

id score

1 25

2 28

3 35

4 45

Thanks,

Re: sas merge

hashman — Thu, 22 Nov 2018 16:16:34 GMT

@nirupama1:

Your code actually works as expected.

However, provided that the ID key-values in EX1 are unique (as is the case with your sample), the tool SAS intends for this kind of job is not MERGE but UPDATE:

data comb ;       
  update ex1 ex2 ;
  by ID ;         
run ;

One of the practical rules Ian Whitlock has formulated for MERGE is that, in order to avoid unexpected results of overwriting, only the BY variables in the data sets being merged should be the same.

Paul D.

Re: sas merge

kiranv_ — Thu, 22 Nov 2018 16:43:31 GMT

you can use sql update too

proc sql;
update ex1 a
set score = (select  b.score from ex2 b
                        
                          where a.id = b.id)
where id in (select id from ex2);