Merging table

Reply
Regular Contributor
Posts: 218

Merging table

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




Trusted Advisor
Posts: 1,137

Re: Merging table

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

Thanks,
Jag
Regular Contributor
Posts: 218

Re: Merging table

Posted in reply to Jagadishkatam

thanks jag, that's helpful

Super Contributor
Posts: 308

Re: Merging table

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;

Super User
Super User
Posts: 7,949

Re: Merging table

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.

Super User
Posts: 10,023

Re: Merging table

Data output;

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

by ID;

if a=1;

call missing(of _all_);

run;

Regular Contributor
Posts: 218

Re: Merging table

Hi Xia,

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

Occasional Contributor
Posts: 10

Re: Merging table

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;

Super User
Posts: 10,023

Re: Merging table

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

New Contributor
Posts: 4

Re: Merging table

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

Contributor
Posts: 26

Re: Merging table

Posted in reply to AbhinavBhatnagar

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    ........

1

1

1

2

2

3

3

3

3

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!

Super Contributor
Posts: 275

Re: Merging table

data set3;

     merge set1(in=a) set2;

     by id;

     if a;

run;

Contributor
Posts: 26

Re: Merging table

that gives me the blanks as explained above.

Super User
Posts: 11,343

Re: Merging table

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;

New Contributor
Posts: 4

Re: Merging table

A simple merge will give the desired output:

data three;

merge one two;

by id;

run;

Ask a Question
Discussion stats
  • 16 replies
  • 481 views
  • 0 likes
  • 11 in conversation