Help using Base SAS procedures

Complex Merging

Reply
Contributor
Posts: 35

Complex Merging

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!

Respected Advisor
Posts: 4,920

Re: Complex Merging

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

PG
PROC Star
Posts: 7,471

Re: Complex Merging

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;

Ask a Question
Discussion stats
  • 2 replies
  • 322 views
  • 0 likes
  • 3 in conversation