Merge..

[new_sas_user_4]

I have a table “A” with details like subscriber_no, name, age, occupation

Eg of one of the records : Adam, 35, Data Analyst

And table “B” with similar info subscriber_no, name, age, occupation  and AN EXTRA COLUMN named CITY

Eg: Adam, 30, Director, Toronto

Ella, 40, Manager, Vancouver

I want the info in the final merged table as :

if the subscriber_no exists in table A, take all the information for that subscriber from table A

and just add the CITY : Toronto to it

say, Name : Adam, 35, Data Analyst, Toronto

I do not want the info from B at all EXCEPT adding CITY if the subscriber no exists in A.

However, if the subscriber no does not exist in A then I want all information about it from B

So the final tbale should look like:

Adam, 35, Data Analyst, Toronto

Ella, 40, Manager, Vancouver

How can I achieve this?

Thanks in advance:)

[PeterClemmensen]

I don't see an actual value for subscriber_no in  your example data?

[SuryaKiran]

Do a left join 

proc sql;
create table want as 
select a.*,b.city
from tableA a
left join tableB b on (a.name=b.name);
quit;

If your real data is joining by name, then what if you have multiple people with same names (mostly possible)?

[new_sas_user_4]

Table A

001,Adam, 35, Data Analyst

Table B

001,Adam, 30, Director, Toronto

002, Ella, 40, Manager, Vancouver

I want Table C(final table) as 

001,Adam, 35, Data Analyst, Toronto

002, Ella, 40, Manager, Vancouver

Left join wont get me the subs from table B(which arent in table A)

there are no duplicate names/subscriber no

[ballardw]

---

@new_sas_user_4 wrote:

I have a table “A” with details like subscriber_no, name, age, occupation

Eg of one of the records : Adam, 35, Data Analyst

And table “B” with similar info subscriber_no, name, age, occupation  and AN EXTRA COLUMN named CITY

Eg: Adam, 30, Director, Toronto

Ella, 40, Manager, Vancouver

I want the info in the final merged table as :

if the subscriber_no exists in table A, take all the information for that subscriber from table A

and just add the CITY : Toronto to it

say, Name : Adam, 35, Data Analyst, Toronto

I do not want the info from B at all EXCEPT adding CITY if the subscriber no exists in A.

However, if the subscriber no does not exist in A then I want all information about it from B

So the final tbale should look like:

Adam, 35, Data Analyst, Toronto

Ella, 40, Manager, Vancouver

How can I achieve this?

Thanks in advance:)

---

Are the values for subscriber_no, name, age, occupation duplicated within table A? If not perhaps:

proc sort data=tablea;
   by subscriber_no name age occupation;
run;

proc sort data=tableb;
   by subscriber_no name age occupation;
run;

data want;
   update tablea
          tableb(keep = subscriber_no name age occupation city)
   ;
   by subscriber_no name age occupation;
run;

[new_sas_user_4]

The aboev solution is giving the error:

BY variable CITY is not on input data set WORK.A

Do both the data sets need to have the column CITY to use update statement?

Also, for the proc sort statement can I just use 

by subscriber_no

instead of subsriber_no name age occupation(all variables) ??

[Reeza]

This would be much easier to answer if you could provide a better sample input data set and expected output. You'll get a working answer much faster. 

---

@new_sas_user_4 wrote:

The aboev solution is giving the error:

BY variable CITY is not on input data set WORK.A

Do both the data sets need to have the column CITY to use update statement?

Also, for the proc sort statement can I just use 

by subscriber_no

instead of subsriber_no name age occupation(all variables) ??

---

[new_sas_user_4]

Please see the attached excel.

[ballardw]

---

@new_sas_user_4 wrote:

The aboev solution is giving the error:

BY variable CITY is not on input data set WORK.A

Do both the data sets need to have the column CITY to use update statement?

Also, for the proc sort statement can I just use 

by subscriber_no

instead of subsriber_no name age occupation(all variables) ??

---

Try MERGE instead of update.

[new_sas_user_4]

Using merge :

data C;

set A B;

run;

using this, it gives me tow rows for one subscriber 

1 Adam 35 Data Analyst 1 

1 Adam 40 Director          1 Toronto

I think its because it takes the unique data from both A and B and therefore returns two rows.