DATA Step, Macro, Functions and more

How to get data according to the benchmark of other dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 97
Accepted Solution

How to get data according to the benchmark of other dataset

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


Accepted Solutions
Solution
‎07-22-2017 12:49 PM
Trusted Advisor
Posts: 1,137

Re: How to get data according to the benchmark of other dataset

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;
Thanks,
Jag

View solution in original post


All Replies
Trusted Advisor
Posts: 1,137

Re: How to get data according to the benchmark of other dataset


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;
Thanks,
Jag
Frequent Contributor
Posts: 97

Re: How to get data according to the benchmark of other dataset

Posted in reply to Jagadishkatam

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
a51997015AUG96
a51997014MAY96
b62014043APR13
b62014046MAY13
b62014048JUN13
Trusted Advisor
Posts: 1,137

Re: How to get data according to the benchmark of other dataset

[ Edited ]

 

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:

 

image.png

Thanks,
Jag
Frequent Contributor
Posts: 97

Re: How to get data according to the benchmark of other dataset

Posted in reply to Jagadishkatam

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

SAS Output


company profit date EPS date_event
a51997015AUG96
a51997014MAY96
b62014043APR13
b62014046MAY13
b62014048JUN13
Frequent Contributor
Posts: 97

Re: How to get data according to the benchmark of other dataset

Posted in reply to Jagadishkatam

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

Solution
‎07-22-2017 12:49 PM
Trusted Advisor
Posts: 1,137

Re: How to get data according to the benchmark of other dataset

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;
Thanks,
Jag
Frequent Contributor
Posts: 97

Re: How to get data according to the benchmark of other dataset

Posted in reply to Jagadishkatam

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
a5JAN975AUG96
a5JAN974MAY96
a9JUN964MAY96
b6APR143APR13
b6APR146MAY13
b6APR148JUN13
Trusted Advisor
Posts: 1,137

Re: How to get data according to the benchmark of other dataset

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;
Thanks,
Jag
Frequent Contributor
Posts: 97

Re: How to get data according to the benchmark of other dataset

Posted in reply to Jagadishkatam

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

Frequent Contributor
Posts: 97

Re: How to get data according to the benchmark of other dataset

Posted in reply to Jagadishkatam

 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 


Super User
Super User
Posts: 7,046

Re: How to get data according to the benchmark of other dataset

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

Frequent Contributor
Posts: 97

Re: How to get data according to the benchmark of other dataset

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

Frequent Contributor
Posts: 97

Re: How to get data according to the benchmark of other dataset

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 293 views
  • 0 likes
  • 3 in conversation