How do i achieve the table join/merge with these conditions?

Question

First of all, I am a SAS Developer. Hence, SAS Datastep or PROC SQL(normal SQL) will work in this case.

I currently have 2 tables with structure as below: ca_no is unique

Table A(Master/Primary)    
ca_no   ca_name   assetnm    power     period
1       ali       house      100       201801
2       abu       plane      200       201801
3       abu       plane      300       201802
4       zara      car        400       201801
7       zara      car        500       201805
8       zara      car        600       201806


Table B(Secondary)
ca_no   ca_name   assetnm    period     kickoffdate    carbrand
1       ali       house      201801     1-1-2018       BMW
2       abu       plane      201801     2-1-2018       Audi
5       bernard   tank       201802     3-1-2018       Merc
6       chris     NA         201803     5-2-2018       Ford

I want to merge for those that can match ca_no. Otherwise, join and create new records for those do not have matching ca_no(like 5 and 6 in table B).

sample output:
Table Result
ca_no    ca_name    assetnm    power    period    kickoffdate    carbrand
1        ali        house      100      201801    1-1-2018       BMW
2        abu        plane      200      201801    2-1-2018       Audi
3        abu        plane      300      201802    3-1-2018       Audi
4        zara       car        400      201801    
5        bernard    tank                201802    3-1-2018       Merc
6        chris      NA                  201803    5-2-2018       Ford
7        zara       car        500      201805    
8        zara       car        600      201806

Is there a way to do so?

something like UNION and JOIN in some way each.

Answer 1

This can be done in a vey simple data step. Without trying to be rude, it seems quite a basic question for a SAS developer to ask. I take it you're not that familiar with the software at the moment.

data A;
input ca_no   ca_name $  assetnm $   power     period;
datalines;
1       ali       house      100       201801
2       abu       plane      200       201801
3       abu       plane      300       201802
4       zara      car        400       201801
7       zara      car        500       201805
8       zara      car        600       201806
;
run;

data B;
input ca_no   ca_name $  assetnm $   period     kickoffdate :ddmmyy8.    carbrand $;
format kickoffdate :ddmmyy8.;
datalines;
1       ali       house      201801     1-1-2018       BMW
2       abu       plane      201801     2-1-2018       Audi
5       bernard   tank       201802     3-1-2018       Merc
6       chris     NA         201803     5-2-2018       Ford
;
run;

data want;
merge A (in=a) B (in=b);
by ca_no;
if a or b;
run;

Answer 2

You can use a union with some empty rows:

SELECT A.ca_no, A.ca_name, A.assetnm, A.power, A.period, B.kickoffdate, B.carbrand FROM A LEFT JOIN B ON A.ca_no = B.ca_no <-- This query gets you all A results with B matches.

SELECT B.ca_no, B.ca_name, B.assetnm, '' AS power, B.period, B.kickoffdate, B.carbrand FROM B LEFT JOIN A ON B.ca_no = A.ca_no WHERE A.ca_no IS NULL <-- This query gets you B results not in A.

Now, you only need to convert this query on a UNION one, adding UNION before the second query.

If you need to sort the results, you can do it inside a nested query, and short it later. Example:

SELECT * FROM (

SELECT A.ca_no, A.ca_name, A.assetnm, A.power, A.period, B.kickoffdate, B.carbrand FROM A LEFT JOIN B ON A.ca_no = B.ca_no

UNION SELECT B.ca_no, B.ca_name, B.assetnm, '' AS power, B.period, B.kickoffdate, B.carbrand FROM B LEFT JOIN A ON B.ca_no = A.ca_no WHERE A.ca_no IS NULL

) AS DerivedTbl1 ORDER BY ca_no

I've write the query without testing it, maybe they can be some typo but that's how you can achieve your needs.

Answer 3

You can try it with full outer join along with coalesce function as:

select coalesce(a.ca_no,b.ca_no), coalesce(a.ca_name,b.ca_name),
coalesce(a.assetnm,b.assetnm),a.power, 
coalesce(a.period,b.period), b.kickoffdate,b.carbrand 
from Table_A a full join Table_B b on a.ca_no=b.ca_no;