topic Re: Merge.. in New SAS User

Merge..

new_sas_user_4 — Thu, 04 Oct 2018 15:35:06 GMT

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:)

Re: Merge..

PeterClemmensen — Thu, 04 Oct 2018 15:40:57 GMT

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

Re: Merge..

SuryaKiran — Thu, 04 Oct 2018 15:42:26 GMT

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)?

Re: Merge..

new_sas_user_4 — Thu, 04 Oct 2018 15:47:44 GMT

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

Re: Merge..

ballardw — Thu, 04 Oct 2018 15:52:23 GMT

@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;

Re: Merge..

new_sas_user_4 — Thu, 04 Oct 2018 16:15:48 GMT

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) ??

Re: Merge..

Reeza — Thu, 04 Oct 2018 16:21:08 GMT

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) ??

Re: Merge..

new_sas_user_4 — Thu, 04 Oct 2018 16:38:01 GMT

Please see the attached excel.

Re: Merge..

ballardw — Thu, 04 Oct 2018 17:27:54 GMT

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

Re: Merge..

new_sas_user_4 — Thu, 04 Oct 2018 17:46:55 GMT

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.