%let ds=tick_out_all;
proc sort data=&ds.; by tick tradingday timesec; run;quit;
data &ds._first(keep=tick tradingday timesec lp_ rename=(lp_=lp_first)); set &ds.; by tick tradingday; if first.tradingday;run;quit; data &ds._last (keep=tick tradingday timesec lp_ rename=(lp_=lp_last)); set &ds.; by tick tradingday; if last.tradingday;run;quit;
/*get ret and alike, merge outcome timesec=93100000 is gone, evne SAS log says not!? */ data &ds._; merge &ds. &ds._first &ds._last; by tick tradingday; run;quit; proc sql; create table &ds._2 as select a.*, b.lp_first, c.lp_last from &ds. as a left join &ds._first as b on a.tick=b.tick and a.tradingday=b.tradingday left join &ds._last as c on a.tick=c.tick and a.tradingday=c.tradingday order by a.tick, a.tradingday, a.timesec; quit;
proc sort data=&ds.; by tick tradingday timesec; run;quit;
%let ts=93100000; data tick_out_all_ts_(keep=); set tick_out_all_(where=(timesec=&ts.)); run;quit; data tick_out_all_ts_2(keep=); set tick_out_all_2(where=(timesec=&ts.)); run;quit;
______________________________________________________________________
3816 data &ds._; merge &ds. &ds._first &ds._last; 3817 by tick tradingday; 3818 run;
NOTE: There were 19397784 observations read from the data set WORK.TICK_OUT_ALL. NOTE: There were 82194 observations read from the data set WORK.TICK_OUT_ALL_FIRST. NOTE: There were 82194 observations read from the data set WORK.TICK_OUT_ALL_LAST. NOTE: The data set WORK.TICK_OUT_ALL_ has 19397784 observations and 71 variables. NOTE: Compressing data set WORK.TICK_OUT_ALL_ decreased size by 29.16 percent. Compressed is 119484 pages; un-compressed would require 168677 pages. NOTE: DATA statement used (Total process time): real time 1:06.84 cpu time 1:02.64
3855 proc sql; 3856 create table &ds._2 as 3857 select a.*, b.lp_first, c.lp_last 3858 from &ds. as a 3859 left join &ds._first as b 3860 on a.tick=b.tick and a.tradingday=b.tradingday 3861 left join &ds._last as c 3862 on a.tick=c.tick and a.tradingday=c.tradingday 3863 order by a.tick, a.tradingday, a.timesec; NOTE: Compressing data set WORK.TICK_OUT_ALL_2 decreased size by 29.16 percent. Compressed is 119484 pages; un-compressed would require 168677 pages. NOTE: Table WORK.TICK_OUT_ALL_2 created, with 19397784 rows and 71 columns.
3864 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1:46.56 cpu time 1:25.90
3873 3874 data tick_out_all_ts_(keep=); set tick_out_all_(where=(timesec=&ts.)); run;
NOTE: There were 0 observations read from the data set WORK.TICK_OUT_ALL_. WHERE timesec=93100000; NOTE: The data set WORK.TICK_OUT_ALL_TS_ has 0 observations and 71 variables. NOTE: DATA statement used (Total process time): real time 30.95 seconds cpu time 7.25 seconds
3874! quit; 3875 data tick_out_all_ts_2(keep=); set tick_out_all_2(where=(timesec=&ts.)); run;
NOTE: There were 82194 observations read from the data set WORK.TICK_OUT_ALL_2. WHERE timesec=93100000; NOTE: The data set WORK.TICK_OUT_ALL_TS_2 has 82194 observations and 71 variables. NOTE: Compressing data set WORK.TICK_OUT_ALL_TS_2 decreased size by 40.70 percent. Compressed is 424 pages; un-compressed would require 715 pages. NOTE: DATA statement used (Total process time): real time 34.50 seconds cpu time 11.07 seconds
3875! quit;
... View more
