topic Re: Merging in SAS or joining in SQL in SAS Enterprise Guide

Merging in SAS or joining in SQL

sasuser0912 — Sat, 21 Jul 2018 15:14:12 GMT

I have the following two tables and I am trying to merge them by Account and Date. It is however not giving me the output that I need.

Table 1 contains Account column, Dale_old and Amount_old are populated for first row and the others rows are missing.

Account	Paid	Date_old	Amount_old
1	1	Jan-17	500
1	0	.	.
1	0	.	.
1	0	.	.
1	0	.	.
1	0	.	.

Table 2 contains all fields populated.

Account	Paid	Date_new	Amount_new
1	0	Oct-16	200
1	0	Nov-16	300
1	0	Dec-16	600
1	0	Jan-17	700
1	0	Feb-17	500
1	0	Mar-17	200

Below is the table that I want to produce after a SAS merge or Join in Sql.

Account	Date_old	Date_new	Amount_old	Amount_new
1	Jan-17	Jan-17	500	700

I have tried using the following code but it doesn't seem to work.

proc sql;

create table test as

select A.*, B.* from table1 as A

left join table2 as B on

A.Account=B.Account

and

A.Date_old=B.Date_new;

quit;

run;

Re: Merging in SAS or joining in SQL

kiranv_ — Sat, 21 Jul 2018 15:42:58 GMT

you need to a do your inner join. Both date_new and data_old have same values and keeping them in final output does not make it usefu.

proc sql;

create table test as

select A.account, 
      date_old, 
      date_new,
      amount_old, 
      amount_new 
from have1 as A

inner join have2 as B 
on        A.Account=B.Account
and       A.Date_old=B.Date_new;

quit;

Re: Merging in SAS or joining in SQL

sasuser0912 — Sat, 21 Jul 2018 15:48:56 GMT

Thanks for your reply. I tried this on my dataset but it is giving me nothing, no output.

Re: Merging in SAS or joining in SQL

kiranv_ — Sat, 21 Jul 2018 16:13:37 GMT

i did something like below and it worked

data have1;
infile datalines dlm ='09'x;
input Account	Paid	Date_old:anydtdte.	Amount_old;
format date_old date9.;
datalines;
1	1	Jan17	500
1	0	.	    .
1	0	.	    .
1	0	.	    .
1	0	.	    .
1	0	.	    .
;

data have2;
infile datalines dlm ='09'x;
input Account	Paid	Date_new:anydtdte.	Amount_new;
format date_new date9.;
datalines;
1	0	Oct16	200
1	0	Nov16	300
1	0	Dec16	600
1	0	Jan17	700
1	0	Feb17	500
1	0	Mar17	200
;

proc sql;

create table test as

select A.account, 
      date_old, 
          date_new,
      amount_old, 
      amount_new 
from have1 as A

inner join have2 as B on

A.Account=B.Account

and A.Date_old=B.Date_new;

quit;

Re: Merging in SAS or joining in SQL

sasuser0912 — Sun, 22 Jul 2018 20:17:26 GMT

Thanks kiranv. Yes this solution works.