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?
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;
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;
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.
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.
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).
How can I fix the format (7670 ~= 7579) in order to be the same in both cases?
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.
But even if I change the format, will the numeric values remain different?
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
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).
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;
Many thanks Reeza, it worked!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.