Desktop productivity for business analysts and programmers

Merging in SAS or joining in SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Merging in SAS or joining in SQL

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.

AccountPaidDate_oldAmount_old
11Jan-17500
10..
10..
10..
10..
10..

 

Table 2 contains all fields populated.

AccountPaidDate_newAmount_new
10Oct-16200
10Nov-16300
10Dec-16600
10Jan-17700
10Feb-17500
10Mar-17200

 

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

AccountDate_oldDate_newAmount_oldAmount_new
1Jan-17Jan-17500700

 

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;

 


Accepted Solutions
Solution
a month ago
PROC Star
Posts: 549

Re: Merging in SAS or joining in SQL

Posted in reply to sasuser0912

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;

View solution in original post


All Replies
PROC Star
Posts: 549

Re: Merging in SAS or joining in SQL

Posted in reply to sasuser0912

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;
Occasional Contributor
Posts: 9

Re: Merging in SAS or joining in SQL

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

Solution
a month ago
PROC Star
Posts: 549

Re: Merging in SAS or joining in SQL

Posted in reply to sasuser0912

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;
Occasional Contributor
Posts: 9

Re: Merging in SAS or joining in SQL

Thanks kiranv. Yes this solution works.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 152 views
  • 0 likes
  • 2 in conversation