BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Costasg
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

11 REPLIES 11
sascom10
Calcite | Level 5

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;


Costasg
Calcite | Level 5

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.

sascom10
Calcite | Level 5

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.

Anotherdream
Quartz | Level 8

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

Costasg
Calcite | Level 5

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

sascom10
Calcite | Level 5

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.


Costasg
Calcite | Level 5

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

LarryWorley
Fluorite | Level 6

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

Costasg
Calcite | Level 5

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

Reeza
Super User

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;

Costasg
Calcite | Level 5

Many thanks Reeza, it worked!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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