topic Re: Merging table in SAS Programming

Merging table

mlogan — Mon, 27 Apr 2015 00:42:31 GMT

Hi All,

I have two table (table 1 and table 2) Can anyone tell me how to merge two table by adding right table (table 2 in my example) data to the first record of the left table (table 1 in my example) only. I wrote the following code, but it writes the right table info beside all left table observations.

Data output;

merge table1(in=a) table2(in=b);

by ID;

if a=1;

run;

TABLE 1 (data is sorted by ID and then date)

ID Date

101 Jan-01-2015

101 Jan-05-2015

101 Jan-07-2015

102 Feb-01-2015

102 Feb-06-2015

103 Mar-01-2015

TABLE 2

ID City

101 Dallas

102 Texas

103 Chicago

OUTPUT

ID Date City

101 Jan-01-2015 Dallas

101 Jan-05-2015

101 Jan-07-2015

102 Feb-01-2015 Texas

102 Feb-06-2015

103 Mar-01-2015 Chicago

Re: Merging table

Jagadishkatam — Mon, 27 Apr 2015 01:31:49 GMT

Please try to use first. and make the other records for city variable blank

Data output;

merge table1(in=a) table2(in=b);

by ID;

if a=1;

if not first.id then city='';

run;

Thanks,

Jag

Re: Merging table

mlogan — Mon, 27 Apr 2015 02:20:03 GMT

thanks jag, that's helpful

Re: Merging table

Loko — Mon, 27 Apr 2015 10:59:02 GMT

Hello,

Another solution:

data want;
merge table1(in=a) table2(in=b rename=(city=_city));
by ID;

if first.id then city=_city;
if a=1;

drop _city;
run;

Re: Merging table

RW9 — Mon, 27 Apr 2015 12:06:05 GMT

Hi,

Err, from my side the first question would be why? To join something to attach another dataset based on a set of id variables. What you mention isn't necessarily repeatable or logical. For instance, if I re-sort your data, the one that merged before may not necessarily merge to the same. What happens in this event:

101 01jan14 c

101 01jan14 a

With this as is then Dallas would end up on row 1, if its sorted by the third variable it would end up on row 2. So the concept of merging based on row position in a dataset is not an advisable method. To be clear, make sure your id variables identify the distinct group you want to attach data to, if this isn't possible with the data, then add another variable in:

First_var

101 01jan14 c No

101 01jan1a a Yes

Then use that variable in the merge, this makes it easily repeatable irrespective of sort. Also note, there are also better options if this is just to arrange the print output, such as group/order in proc report.

Re: Merging table

Ksharp — Mon, 27 Apr 2015 12:53:05 GMT

Data output;

merge table1(in=a) table2(in=b);

by ID;

if a=1;

call missing(of _all_);

run;

Re: Merging table

mlogan — Wed, 29 Apr 2015 14:14:58 GMT

Hi Xia,

I tried your code did not work, would you please make sure that this is the right code. Thanks for your help.

Re: Merging table

Malv72 — Wed, 29 Apr 2015 14:22:51 GMT

Hello

Would this give you the results you are looking for?

proc sql;

create table output as

select a.id

, a.date

, b.city

from table1 as a

left join

table2 as b

on a.id = b.id

;

quit;

Re: Merging table

Ksharp — Thu, 30 Apr 2015 12:28:33 GMT

OK. I should test it before posting it .

data TABLE1 ;

input ID Date : $20.;

cards;

101 Jan-01-2015

101 Jan-05-2015

101 Jan-07-2015

102 Feb-01-2015

102 Feb-06-2015

103 Mar-01-2015

;

run;

data TABLE2 ;

input ID City : $20.;

cards;

101 Dallas

102 Texas

103 Chicago

;

run;

Data output;

merge table1(in=a) table2(in=b);

by ID;

if a=1 then output;

call missing(City);

run;

Xia Keshan

Re: Merging table

AbhinavBhatnagar — Mon, 08 Jun 2015 05:37:53 GMT

Hi,

Below will give you the desired output:

data try;

merge one two;

by ID;

if first.id then city=city;

else city=" ";

run;

Thanks

Re: Merging table

Shirin — Mon, 08 Jun 2015 20:24:30 GMT

Hello,

I can't seem to be able to start a new discussion, as my question is also about merging, I thought it may be appropriate to ask here, as the original question seems to have been answered.

I am trying to do a sas merge and I am getting a lot of blanks.

I'm doing a many to 1 merge.

Data set 1:

ID Var1 Var2 Var3 ........

Data set 2

ID VarA

1 x

2 u

3 h

I want a third dataset, that merges on the ID, has the full data set 1 and adds the VarA from Data set 2.

I'm doing a merge but it's only filling in VarA for the first observation for each ID and leaves the rest blank.

Any help would be appreciated.

Many thanks!

Re: Merging table

slchen — Mon, 08 Jun 2015 20:58:06 GMT

data set3;

merge set1(in=a) set2;

by id;

if a;

run;

Re: Merging table

Shirin — Mon, 08 Jun 2015 21:18:19 GMT

that gives me the blanks as explained above.

Re: Merging table

ballardw — Mon, 08 Jun 2015 21:20:59 GMT

proc sql;

create table want as

select a.*, b.vara

from table1 as a left join table2 as b

on a.Id=b.id;

quit;

Re: Merging table

AbhinavBhatnagar — Thu, 11 Jun 2015 04:56:30 GMT

A simple merge will give the desired output:

data three;

merge one two;

by id;

run;

Re: Merging table

Shirin — Thu, 11 Jun 2015 11:21:45 GMT

NO it doesn't, I keep getting blanks.

Re: Merging table

Kurt_Bremser — Thu, 11 Jun 2015 12:07:49 GMT

data table1;

input ID Var1 Var2;

cards;

1 1 1

1 2 2

1 3 3

2 1 1

2 2 2

3 1 1

3 2 2

3 3 3

3 4 4

;

run;

data table2;

input ID VarA $;

cards;

1 x

2 u

3 h

;

run;

data table3;

merge table1 table2;

by ID;

run;

proc print;run;

gets this output:

Obs

Var1

Var2

VarA

1	1	1	1	x
2	1	2	2	x
3	1	3	3	x
4	2	1	1	u
5	2	2	2	u
6	3	1	1	h
7	3	2	2	h
8	3	3	3	h
9	3	4	4	h