Merging datasets with 2 different time variables in SAS

Hye Guys,

for those regularly browsing this site sorry for already another question (however I did solve my last question myself!)

I have another problem with merging datasets, it seems that accounting for time in datasets is a real pain in the ass. I succesfully managed to merge on months in my previous datasets, however it seems I have a final dataset which only has quarter as a time count variable. So where all my normal data开发者_Go百科bases have month 1- xxx as an indicator of time, this database had quarter as an indicator of time.

I still want to add the variables of this last database, let's call it TVOL, into my WORK database.

Quick summary

QUARTER: Quarter 0 = JAN1996-MAR1996

Month: Month 0 = JAN1996

Example: TVOL

TVOL _______ Ticker __________ Quarter

1500 _______ AA ________________ -1

52546 _______ BB ________________ 15

Example: WORK

BETA _______ Ticker __________ Month

1.52 _______ AA ________________ 2

1.54_______ BB ________________ 3

Example: Merged:

BETA _______________ TVOL _______ Ticker __________ Month

1.52 ________________ 500 _________ AA ________________ 2

I now want to merge this 2 tables using following relationship

if the month is in quarter 1, the data of quarter 0 has to be used, so if i have an observation i nWORK with date 2FEB1996 the TVOL of quarter -1 has to be put behind this observation.

Something like IF month = quarter i use data quarter i-1.

Also, as TVOL is measured quarterly and I have to put in monthly I have to take the average, so (TVOL/3) should be added as a variable.

Thanks!

Ok, so I solved my problem!

 data test;
    set test;
    Quarter=intck('qtr','01apr96'd,recdats);
    put _all_;
run;
proc sort data=test;
by ticker quarter;
run;
proc sort data=wtvol;
by ticker quarter;
run;
data test;
merge test(in=a) wtvol(in=b);
by ticker quarter;
frommerg=a;
fromwtvol=b;
run;
data test;
set test;
if frommerg=0 then delete;
run;
data test;
set test;
if fromwtvol = 0 then delete;
run;
data test;
set test;
drop frommerg fromwtvol;
run;

I created a quarter variable in my base dataset and merged the 2 sets based on quarter and ticker.