BookmarkSubscribeRSS Feed
Benn
Calcite | Level 5

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!

2 REPLIES 2
PGStats
Opal | Level 21

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
art297
Opal | Level 21

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;

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
  • 2 replies
  • 1014 views
  • 0 likes
  • 3 in conversation