How to get data according to the benchmark of other dataset

[lixuan]

Hi, I ask the question 2 days before , but there is a new problem . The data just like following . I wanna  pick the data from dataset 'have_event'  if it's date is in 12 months before the date of dataset 'have_base'.  I also show the result I want and the code i tried wihch didn't work.

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;
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 I want should be :

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

for example , as for  a, The date in dataset 'have_base'  is  '199701' and '199606', so the date in 'have_event' dataset  should be (199601 to 199612) and in(199506 to 199605) and I get the right EPS.

Thanks&regards

[Jagadishkatam]

proc sql;
create table test as select a.*,b.eps,b.date as date_event format=monyy6. from have_base as a, have_event as b where intnx('month',a.date, -12)<=b.date<=a.date;
quit;

[lixuan]

Thank you, I changed your code and ran it .

proc sql;
create table test as select a.*,b.eps,b.date as date_event format=monyy6.
from have_base as a, 
	 have_event as b
where intnx('month',a.date, -12)<=b.date<=a.date
	 &a.company=b.company;
quit;

The result is follwing ,but actually it skips one observation:a 199605 4(EPS). Because the second record in 'have_base' has a date 199606, and I also wanna get the record in 'have_event' which is in 12 months before 199606.

company profit date EPS date_event

a	5	199701	5	AUG96
a	5	199701	4	MAY96
b	6	201404	3	APR13
b	6	201404	6	MAY13
b	6	201404	8	JUN13

[Jagadishkatam]

I followed the same getting your expected output. Could you please check again

proc sql;
create table test as select a.*,b.eps,b.date as date_event format=monyy6. from have_base as a, have_event as b where intnx('month',a.date, -12)<=b.date<=a.date
and a.company=b.company;
quit;

output:

[lixuan]

How could it happen? I copy your codes without change, but my result is 

SAS Output

company profit date EPS date_event

a	5	199701	5	AUG96
a	5	199701	4	MAY96
b	6	201404	3	APR13
b	6	201404	6	MAY13
b	6	201404	8	JUN13

[lixuan]

Did you chang my input before? Could you post all the codes? thank u

[Jagadishkatam · Accepted solution]

I did not change anything except the format monyy6.

data have_base;
input company$ _date:$6. profit;
date = input(_date !! '01',yymmdd8.);
format date  monyy6.;
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 monyy6.;
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;


proc sql;
create table test as select a.*,b.eps,b.date as date_event format=monyy6. from have_base as a, have_event as b where intnx('month',a.date, -12)<=b.date<=a.date
and a.company=b.company;
quit;

[lixuan]

Yeah, I get the same result with yours, but I don't know why. In my opinion, format should not influence the result. Do you know the reason?

company profit date EPS date_event

a	5	JAN97	5	AUG96
a	5	JAN97	4	MAY96
a	9	JUN96	4	MAY96
b	6	APR14	3	APR13
b	6	APR14	6	MAY13
b	6	APR14	8	JUN13

[Jagadishkatam]

I agree with you the format should not influence, i ran the code again with the earlier format yymmn6. that you used and i get the same  result

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;
proc sort data=have_event;
by company;
run;


proc sql;
create table test as select a.*,b.eps,b.date as date_event  from have_base as a, have_event as b where intnx('month',a.date, -12)<=b.date<=a.date
and a.company=b.company;
quit;

[lixuan]

OK, thank you very much. I will try it again. You do me a good favor!

[lixuan]

 Hi ,Jag. Whatever I try, I can't get the wrong result. I don't know, maybe it's just the software's mistake. thank you 

[Tom]

You have two base dates for company 'a'.  Which one do you want to use?

[lixuan]

I want use both. For example, in 'have_base' dataset, somthing happened in the date, and in 'have_event' dataset, the character has benn recorded. I want to konw the change of chatacter before or after the date of 'have_base" dataset. Thanks

[lixuan]

Maybe it's a impossible mission. I explain  more detailly. as you see, a in 'have_base' have two records.

First,  date of a  in dataset 'have_event' should be compared  with the first record of a in 'have_base'. If date of a in 'have_event'  is in 12 months before the date of first record in 'have_base', it will be picked out, and I get the first two records of result which contains two a .

Second, I prefer doing the same thing with the second record of 'have_base'. That is treating  the date of second record a in 'have_base' as a benchmark to find the right record in 'have_event'. As a result, I get the third record a in my result.

My english is poor, and I wonder if I express my idea clearly . I hope to get your help, thank you.