topic Re: How to get data according to the benchmark of other database in SAS Programming

How to get data according to the benchmark of other database

lixuan — Thu, 20 Jul 2017 07:51:19 GMT

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;

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

Kurt_Bremser — Thu, 20 Jul 2017 08:24:43 GMT

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

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

lixuan — Thu, 20 Jul 2017 09:01:21 GMT

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.

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

Kurt_Bremser — Thu, 20 Jul 2017 11:01:33 GMT

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

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

lixuan — Thu, 20 Jul 2017 12:08:27 GMT

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

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

lixuan — Sat, 22 Jul 2017 06:29:09 GMT

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

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

lixuan — Sat, 22 Jul 2017 15:43:32 GMT

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 .