How to get data according to the benchmark of other database

[lixuan]

Hi, I have two database like the following. Maybe data  'have' is a event-happen day and data 'want' is some characters of the company. What i wanna is to find out the change of EPS of the company before and after the event-day, so I hope to get the data of the same company from database want and have. The time is 12 monthes before and 12 monthes after event day (the date of data have). I feel its so hard for me .

data have;
input company$ date  profit;
datalines;
a 199701 5
b 201404 6
f 200004 78
;
run;

data want
input company$ date  EPS;
datalines;
a 199701 5
c 201404 6
k 200004 78
n 198607 56
a 198504 3
b 197604 3
;
run; 

[Kurt_Bremser]

Could you give us the example how the resulting dataset should look like?

[lixuan]

data have;
input company$ date  profit;
datalines;
a 199701 5
b 201404 6
f 200004 78
;
run;

data want
input company$ date  EPS;
datalines;
a 199608 5
a 199706 30
a 199605 4
a 199712 9
a 199806 10
c 201404 6
k 200004 78
n 198607 56
a 198504 3
b 201304 3
b 201305 6
b 201306 8
b 201504 80
b 201603 5

;
run; 

/*the result should be*/
data rusult;
input company$ date  EPS;
datalines;
a 199608 5
a 199706 30
a 199605 4
a 199712 9
b 201304 3
b 201305 6
b 201306 8
b 201504 80 
;
run;

ok, I want the result as above. Because 'a' and 'b' are also in data 'have', they should be selected. As for 'b', the date in 'have' is 201404, so the date in 'want' should be in (201304 to 201303) and in (201405 to 201504), and I get the 'EPS' I want. The last observation of b in data 'want' should be excluded for the date is out of one year range. I don't know if I express my idea well enough to be understood. If any question , pls let me know. Thank u very much.

[Kurt_Bremser]

Look at this:

data have_base;
input company$ _date:$6. profit;
date = input(_date !! '01',yymmdd8.);
format date yymmn6.;
drop _date;
datalines;
a 199701 5
b 201404 6
f 200004 78
;
run;

data have_event;
input company$ _date:$6. EPS;
date = input(_date !! '01',yymmdd8.);
format date yymmn6.;
drop _date;
datalines;
a 199608 5
a 199706 30
a 199605 4
a 199712 9
a 199806 10
c 201404 6
k 200004 78
n 198607 56
a 198504 3
b 201304 3
b 201305 6
b 201306 8
b 201504 80
b 201603 5
;
run;

proc sort data=have_event;
by company;
run;

data want;
merge
  have_base (in=b rename=(date=date_b))
  have_event (in=e)
;
by company;
if b and e;
if abs(intck('month',date,date_b)) <= 12;
drop profit date_b;
run;

proc print data=want noobs;
run;

Result:

company    EPS      date

   a         5    199608
   a        30    199706
   a         4    199605
   a         9    199712
   b         3    201304
   b         6    201305
   b         8    201306
   b        80    201504

[lixuan]

Oh, great!  it's amazing! You give me a great help. Thanks a lot

[lixuan]

Hi, I tried to get the data not in pre and after benchmark time, insteadly I wanna get the datas before the benchmark,  so i revised the code , but it didin't wok. Could you help me again? Thanks

data have_base;
input company$ _date:$6. profit;
date = input(_date !! '01',yymmdd8.);
format date yymmn6.;
drop _date;
datalines;
a 199701 5
b 201404 6
f 200004 78
;
run;

data have_event;
input company$ _date:$6. EPS;
date = input(_date !! '01',yymmdd8.);
format date yymmn6.;
drop _date;
datalines;
a 199608 5
a 199706 30
a 199605 4
a 199712 9
a 199806 10
c 201404 6
k 200004 78
n 198607 56
a 198504 3
b 201304 3
b 201305 6
b 201306 8
b 201504 80
b 201603 5
;
run;

proc sort data=have_event;
by company;
run;

data want;
merge
  have_base (in=b rename=(date=date_b))
  have_event (in=e)
;
by company;
if b and e;
/*if date >= intnx('month',date_b, -12);*/
if 0>=intck('month',date,date_b) >=-12;
drop profit date_b;
run;

proc print data=want noobs;
run;

The result 

company EPS date

a	10	199806
b	80	201504
b	5	201603

[lixuan]

Hi, I got the answer use the code.

data want;
merge
  have_base (in=b rename=(date=date_b))
  have_event (in=e)
;
by company;
if b and e;
if intnx('month',date_b, -12)<=date<=date_b;
/*if 0>=intck('month',date,date_b) >=-12;*/
drop profit date_b;
run;

 But actually my datas' structure is like this, and a in dataset 'have_base' has duplicated observations. But I also want it to be a benchmark.

data have_base;
input company$ _date:$6. profit;
date = input(_date !! '01',yymmdd8.);
format date yymmn6.;
drop _date;
datalines;
a 199701 5
a 199606 9
b 201404 6
f 200004 78
;
run;

data have_event;
input company$ _date:$6. EPS;
date = input(_date !! '01',yymmdd8.);
format date yymmn6.;
drop _date;
datalines;
a 199608 5
a 199706 30
a 199605 4
a 199712 9
a 199806 10
c 201404 6
k 200004 78
n 198607 56
a 198504 3
b 201304 3
b 201305 6
b 201306 8
b 201504 80
b 201603 5
;
run;

I want to get the result :

a 199608 5
a 199605 4
a 199605 4
b 201304 3
b 201305 6
b 201306 8

actually, I have no any idea of that. Thanks a lot .