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
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, that's helpful
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;
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.
Data output;
merge table1(in=a) table2(in=b);
by ID;
if a=1;
call missing(of _all_);
run;
Hi Xia,
I tried your code did not work, would you please make sure that this is the right code. Thanks for your help.
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;
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
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
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!
data set3;
merge set1(in=a) set2;
by id;
if a;
run;
that gives me the blanks as explained above.
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;
A simple merge will give the desired output:
data three;
merge one two;
by id;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.