BookmarkSubscribeRSS Feed
mlogan
Lapis Lazuli | Level 10

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




16 REPLIES 16
Jagadishkatam
Amethyst | Level 16

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
mlogan
Lapis Lazuli | Level 10

thanks jag, that's helpful

Loko
Barite | Level 11

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

Data output;

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

by ID;

if a=1;

call missing(of _all_);

run;

mlogan
Lapis Lazuli | Level 10

Hi Xia,

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

Malv72
Calcite | Level 5

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;

Ksharp
Super User

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

AbhinavBhatnagar
Calcite | Level 5

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

Shirin
Obsidian | Level 7

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!

slchen
Lapis Lazuli | Level 10

data set3;

     merge set1(in=a) set2;

     by id;

     if a;

run;

Shirin
Obsidian | Level 7

that gives me the blanks as explained above.

ballardw
Super User

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;

AbhinavBhatnagar
Calcite | Level 5

A simple merge will give the desired output:

data three;

merge one two;

by id;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 1339 views
  • 0 likes
  • 11 in conversation