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;
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;
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;
Thanks for your reply. I tried this on my dataset but it is giving me nothing, no output.
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;
Thanks kiranv. Yes this solution works.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.