The query requires remerging summary statistics back with the original data.

[shawnchen0321]

Hi, I have a question. 

My origin data, J_4, have two variables (code and new_event_date),

and L_2 have three variables (code, date_2, and prc).

I want to merge them together based on the same code (code=code) and date (having period = min(abs(period))),

but my log always shows that "The query requires remerging summary statistics back with the original data."

Does anybody know why it keep doing this?

proc sql;

 create table L_3 as
 select distinct
 a.code, a.new_event_date,
 b.date_2, b.prc,
 b.date_2-a.new_event_date as period
 from J_4  as a, L_2  as b

 where a.code=b.code
 and-5<=intck('day',a.new_event_date,b.date_2)<=5

 group by a.code, a.new_event_date

 having period = min(abs(period));
quit;

Thanks for your help.

[Tom]

Because you are selecting 5 variables and grouping by only two of them. 

Is there any reason why you don't want it it remerge the maximum back to all of the records. How else will it be able to compare it to the value or period on every record?

Also why do you want to exclude groups where the largest absolute value of period happens to be negative?

[shawnchen0321]

Hi, expert.

Let me briefly describe what information I want.

In the data set of J_4, there are only three different companies that release news on a certain event day, and I want to know the stock price on that event day, while the data set of L_2 has stock price data of 100 companies every day.

1) Because there are holidays in the stock market, it does not necessarily correspond to the stock price on an event day, so I choose the closest date as the stock price on the event day. Therefore, I first find the stock price five days before and after the event day, and then find the stock price closest to the event day. (-5<=intck('day',a.new_event_date,b.date_2)<=5 & having period = min(abs(period))  )

2) This is why I use 'group by' a certain company and its event day. I only hope that the event day of the merged company has only one stock price data. (group by a.code, a.new_event_date)

Do you know where I can modify the code to get the information I want?

Thank you in advance.

[Tom]

In that case it is easier to INTERLEAVE the data and just REMEMBER the stock price.

So if you have EVENTS with COMPANY, DATE, other variables.

And you have QUOTES with COMPANY, DATE and PRICE

then you want to do this:

data want;
  set quotes(in=in1 keep=company date price) events(in=in2);
  by company date;
  retain last_price ;
  if first.company then last_price=.;
  if in1 then last_price=price;
  if in2;
  drop price;
run;

[shawnchen0321]

Hi, expert.

Thanks for your help, but that code doesn't work.

I type the code and example below to provide information.

data event;
input code $ date yymmdd10.;
format date yymmdd10.;
cards;
001 2010-01-04
002 2015-03-21
;
run;

data prc;
input code $ date yymmdd10. prc;
format date yymmdd10.;
cards;
001 2010-01-01 9
001 2010-01-02 11
001 2010-01-03 10
001 2010-01-05 11
001 2010-01-06 12
002 2015-03-17 5
002 2015-03-19 4
002 2015-03-21 5
002 2015-03-22 6
;
run;

data want;
input code $ date yymmdd10. prc prc_3daysago;
format date yymmdd10.;
cards;
001 2010-01-04 10 9
002 2015-03-21 5 5
;
run;

1) Since there are holidays in the stock market,

the stock price on the event day is the stock price on the event day or the most recent stock price before the event day.

2) I also need the stock price three days before the event,

the same logic uses the stock price on that day or the most recent one.

3) In this case, is it easier to use proc sql?

Or can it be done with set/retain/merge?

Very appreciate for your help.

Looking forward to your reply.

[Tom · Accepted solution]

Works fine for me. In fact it is trivial to extend it to include the price from three days ago also by just making a copy of the event data with the date adjusted by three days.

data event_pre3;
  set event ;
  date=date-3;
run;

data want;
  merge prc(in=in1) event(in=in2) event_pre3(in=in3);
  by code date ;
  retain last_price prc_3;
  if first.code then call missing(last_price,prc_3);
  if in1 then last_price=prc ;
  if in3 then prc_3=last_price ;
  if in2 ;
  prc=last_price;
  drop last_price;
run;

[shawnchen0321]

This code can work.

I have learned a lot.

Big thanks to you.