topic Complex Merging in SAS Procedures

Complex Merging

Benn — Sat, 12 Jan 2013 19:05:31 GMT

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!

Re: Complex Merging

PGStats — Sat, 12 Jan 2013 19:29:12 GMT

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;

Re: Complex Merging

art297 — Sat, 12 Jan 2013 20:27:13 GMT

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;