Help using Base SAS procedures

Help with merging databases

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

Help with merging databases

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?

Attachment

Accepted Solutions
Solution
‎05-14-2013 07:51 PM
Super User
Posts: 17,899

Re: Help with merging databases

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;

View solution in original post


All Replies
Occasional Contributor
Posts: 15

Re: Help with merging databases

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;


Frequent Contributor
Posts: 75

Re: Help with merging databases

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.

Occasional Contributor
Posts: 15

Re: Help with merging databases

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.

Super Contributor
Posts: 418

Re: Help with merging databases

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).

Frequent Contributor
Posts: 75

Re: Help with merging databases

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

Occasional Contributor
Posts: 15

Re: Help with merging databases

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.


Frequent Contributor
Posts: 75

Re: Help with merging databases

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

Frequent Contributor
Posts: 129

Re: Help with merging databases

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

Frequent Contributor
Posts: 75

Re: Help with merging databases

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).

Solution
‎05-14-2013 07:51 PM
Super User
Posts: 17,899

Re: Help with merging databases

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;

Frequent Contributor
Posts: 75

Re: Help with merging databases

Many thanks Reeza, it worked!

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 470 views
  • 3 likes
  • 5 in conversation