Hi Guys,
I have a complex merging problem which I can't seem to get my head around it.
I have 2 sets of data:
SET A: In this data set, the important feature is that the dates present are the last day of every quarter. i.e 31st Mar, 30th Jun, 30th Sep and 31st Dec
DATE Company Rank
19800331 03134 1
19800331 01593 1
19800331 53935 5
SET B: In this data set, the important feature is that the dates present are daily i.e 1st Jan, 2nd Jan, 3rd Jan............31st Dec.................................
Date Company Price Volume
19800401 03134 15 1500
19800402 03134 14 1000
19800403 03134 14 780
*Imagine that SET B has daily data for all 3 companies all the way till end of June.
What I am try to do is to merge SET A and SET B together, where the Variable 'Rank' of SET A will be merged to SET B daily for the next 3 months for a particular company.
I.e: Since on 19800331, company 03134 has a rank of 1, this rank will follow it for the whole of Apr, May and June in the daily data set B.
Following this, on 19800630, the end of the next quarter, the rank of company 03134 will follow it for the whole of Jul, Aug, Sep.
I am having dificulties merging SET A and B given the above needs, specifically because the usual code that I use requires that the dates be exactly the same:
PROC SQL NOPRINT;
CREATE TABLE PORTFOLIOS
AS SELECT A.RANK, B.DATE, B.COMPANY, B.PRICE, B.VOLUME
FROM DOC as A, NUS as B
WHERE A.DATE=B.DATE and A.COMPANY=B.COMPANY;
Since I only have quarterly dates in SET A, I am unable to use the above codes to merge to SET B anymore.
Thanks in advance to anyone who can help me with this!
Use the INTNX function to do the match :
data A;
format date yymmdd10.;
input DATE :yymmdd8. Company Rank;
datalines;
19800331 03134 1
19800331 01593 1
19800331 53935 5
;
data B;
format date yymmdd10.;
input DATE :yymmdd8. Company Price Volume;
datalines;
19800401 03134 15 1500
19800402 03134 14 1000
19800403 03134 14 780
;
proc sql;
create table C as
select B.*, A.Rank
from B left join A on A.Company=B.Company and
A.date = intnx("QTR",B.date,-1,"END");
select * from C;
quit;
PG
I interpreted your specs slightly differently. Does the following approximate what you want to achieve?:
PROC SQL;
CREATE TABLE PORTFOLIOS
AS SELECT A.RANK, B.DATE, B.COMPANY, B.PRICE, B.VOLUME
FROM DOC as A, NUS as B
WHERE a.date le B.DATE le intnx('month',a.date,3,'end') and
A.COMPANY=B.COMPANY
;
quit;
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.