It really helps to show what you expect the output data set needs to look like.
You should have your example data in the form of data step code. I believe that has been mentioned more than once. Barring that use text pasted into a text box opened on the forum with the </> icon. I had to do a lot of editing of strange characters to make a data step that worked from that formatted table.
Example:
data have;
input CUSIP $ DATE :mmddyy10. return d;
format date mmddyy10.;
datalines;
001855 7/16/1991 0.5 .
001855 7/19/1991 0.025 .
001855 7/22/1991 0.006 .
001855 7/23/1991 0.076 .
001855 7/24/1991 0.001 .
001855 7/25/1991 0.008 .
001855 7/29/1991 0.0015 .
001855 7/30/1991 0.00016 1
001855 8/1/1991 0.0015 .
001855 8/2/1991 0.0016 .
001855 8/5/1991 0.019 .
;
You do not very clearly define what (-120, -3) actually means. Is it days or is it number of records? Your existing code seems to be using number of records and not days.
You also do not say exactly what memory is "full". I suspect that because of the following line that your LOG window gets full of text. You have potentially a a very large number of loops even with a moderate to small data set to start with
put (_n_ nobs CUSIP start stop ) (=);
Since your example data does not have anywhere near enough records to do -120 of anything here is a way to get -5 to -3 RECORDS. I am assuming because the definitions of -120 and such are not provided that you mean within the same cusip group.
data helper;
set have;
where d=1;
firstrec = lineno -5;
if firstrec <0 then firstrec=1;
lastrec = lineno -3;
run;
proc sql;
create table want_5_3 as
select a.cusip, a.date as eventdate
,b.date as tradedate, b.return
from helper as a
left join
have as b
on a.cusip=b.cusip
and a.firstrec le b.lineno le a.lastrec
order by a.cusip,a.date,b.date
;
quit;
This approach would require you to add the lineno, or other variable name to indicate which row of the data set each record belongs on AFTER sorting by Cusip and date.
One of the very likely problems with your code is that if the range of values you request could actually be looking for number of records that do not exist at all because the -120 records, as in your example data would be before the first record.
The SQL uses a range of records calculated for each event date. Note, this should be have with negative or positive numbers just use a +3 or whatever.
IF however -120 is supposed to a date interval then this should work. This again uses -5 and -3 as bounds for Date range:
proc sql;
create table want as
select a.cusip, a.date as eventdate,
b.date as tradedate, b.return
from (select * from have where d=1) as a
left join
have as b
on a.cusip=b.cusip
and (a.date - 5) le b.date le (a.date - 3)
;
quit;
If neither of these approaches yield what you would expect for -5 and -3 then provide what you expect. Please do not use ranges of values that cannot be exercised with your example data.
If you actually write and keep code looking like your example then you are up for ugliest code I have seen on this forum this calendar year, and possibly several before that. Indenting in your friend. I had to indent your code to have a chance of even guessing what was done.
data want_120_21;
start=stop+1;
retain stop 0;
do stop=stop+1 by 1 until(last.CUSIP);
set have end=eof nobs=nobs;
by CUSIP;
end;
put (_n_ nobs CUSIP start stop ) (=);
tradeno=0;
do obs=start to stop;
set have;
if d=1 then do;
tradeno+1;
tradedate=DATE;
format tradedate date9.;
do p=max(obs-120,start) to min(obs-21,stop);
offset=p-obs;
set have point=p;
output;
end;
end;
end;
run;
Click image to register for webinar
