topic Re: Help with merging databases in SAS Procedures

Help with merging databases

Costasg — Tue, 14 May 2013 19:52:57 GMT

Hi everybody,

I have a problem merging 2 databases (of different length of observations)

Here is the code I used;

proc sql;

create table a5 as select

A.qtr,

A.mgrno,

A.ticker,

A.D,

B.N,

B.fraction_mean,

B.frmean_1

from a3 as A left join A4 as B on A.qtr=B.qtr and A.ticker=B.ticker

order by qtr, ticker;

quit;

However it doesn't work. Even with merge statement:

data a5;merge a3 a4;by qtr ticker;run;

Again it doesn't work. Any ideas?

Re: Help with merging databases

sascom10 — Tue, 14 May 2013 20:32:13 GMT

There seem tp be something going on with the format for variable qtr in both the tables. Try running below step and have a look at qtr1 variable which is not formatted. ll ovbserve the difference.

libname locl 'C:\test';

data a3;
set locl.a3;

qtr1=qtr;
*format qtr1 YYQ6.;
run;

data a4;
set locl.a4;

qtr1=qtr;
*format qtr1 YYQ6.;
run;

Re: Help with merging databases

Costasg — Tue, 14 May 2013 20:39:19 GMT

Still it doesn't work. As previously, it doesn't fill all the values. The same ticker could appear more than one time in the same quarter. But it will only fill it once.

Re: Help with merging databases

sascom10 — Tue, 14 May 2013 20:49:33 GMT

Try runngin this and view the the qtr1 var in both again. ll get it -

data a3;
set locl.a3;

qtr1=qtr;
format qtr1 date9.;
run;

data a4;
set locl.a4;

qtr1=qtr;
format qtr1 date9.;
run;

QTR var in A3 and A4 actually ve unmatching valurs. you ll ve to change the var in either of the dataset first and then apply merge/join.

Re: Help with merging databases

Anotherdream — Tue, 14 May 2013 20:49:40 GMT

Sascom10 already answered your question, your variable qtr is not equal in the two dataset, therefor it does not merge or join correctly.

Example: In A3 you ahve 1980-4, however the true numeric value for this is 7670. In dataset A4 you have 1980-4, however the TRUe numeric value for it is 7579.

7670 ~= 7579, therefore it will not join.

Your variable only look like they are the same due to the way you have formatted them (note the format is different in both files).

Re: Help with merging databases

Costasg — Tue, 14 May 2013 21:40:38 GMT

How can I fix the format (7670 ~= 7579) in order to be the same in both cases?

Re: Help with merging databases

sascom10 — Tue, 14 May 2013 21:47:30 GMT

Referencing the 2 steps I mentioned above shift the the date in one of the qtr var such that its matches w qtr of other one.

That is one of the ways I look at it.

Re: Help with merging databases

Costasg — Tue, 14 May 2013 21:55:55 GMT

But even if I change the format, will the numeric values remain different?

Re: Help with merging databases

LarryWorley — Tue, 14 May 2013 22:31:45 GMT

That's right. Changing format will not affect the underlying values.

Add 91 to the smaller value. Then the dates will be equivalent.

It appears that both values are sas dates, with one using first day of quarter and the earlier the last day of quarter. 7579 ==> 10/1/2010 7670 ==>10/31/2010

Re: Help with merging databases

Costasg — Tue, 14 May 2013 22:48:48 GMT

Thank you Larry.

The problem is that not all quarters have 91 days difference. So it doesn't fix the problem entirely.

Is there anyway to convert the dates of the one database to the last day of quarter?

For example using intnx (though I don't know how).

Re: Help with merging databases

Reeza — Tue, 14 May 2013 23:51:47 GMT

Assuming QTR is a date actually ( I haven't looked at your data) you can convert them in the join condition, but you should also change it in your select statement.

proc sql;

create table a5 as select

put(A.qtr, yy6.) as QTR,

A.mgrno,

A.ticker,

A.D,

B.N,

B.fraction_mean,

B.frmean_1

from a3 as A left join A4 as B on put(A.qtr, yyq6.)=put(B.qtr, yyq6.) and A.ticker=B.ticker

order by qtr, ticker;

quit;

Re: Help with merging databases

Costasg — Wed, 15 May 2013 09:32:55 GMT

Many thanks Reeza, it worked!