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®ards
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;
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;
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.
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 |
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:
How could it happen? I copy your codes without change, but my result is
SAS Output
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 |
Did you chang my input before? Could you post all the codes? thank u
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;
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?
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 |
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;
OK, thank you very much. I will try it again. You do me a good favor!
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
You have two base dates for company 'a'. Which one do you want to use?
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.