06-28-2017
rkdubey84
Obsidian | Level 7
Member since
02-11-2013
- 36 Posts
- 33 Likes Given
- 2 Solutions
- 0 Likes Received
-
Latest posts by rkdubey84
Subject Views Posted 1084 04-29-2017 04:21 AM 942 04-29-2017 02:04 AM 993 03-07-2017 11:30 PM 558 03-05-2017 12:44 AM 3869 03-04-2017 04:42 PM 3870 03-04-2017 04:39 PM 3873 03-04-2017 04:22 PM 3394 03-04-2017 03:26 PM 3882 03-04-2017 03:02 PM 3886 03-04-2017 02:44 PM -
Activity Feed for rkdubey84
- Liked Re: PROC CORR for Reeza. 05-03-2017 11:03 AM
- Posted Re: Proc SQL Error on SAS Programming. 04-29-2017 04:21 AM
- Posted Proc SQL Error on SAS Programming. 04-29-2017 02:04 AM
- Liked Re: how to winsorize variable using SAS for Ksharp. 03-21-2017 04:27 AM
- Liked Re: how to winsorize variable using SAS for mkeintz. 03-21-2017 04:25 AM
- Posted Re: Getting Lead Values based on time (Next 1 Minute, Next 5 Minute) on SAS Programming. 03-07-2017 11:30 PM
- Liked Re: Getting Lead Values based on time (Next 1 Minute, Next 5 Minute) for mkeintz. 03-07-2017 11:19 PM
- Liked Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? for Kurt_Bremser. 03-07-2017 11:17 PM
- Liked Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? for Ksharp. 03-05-2017 05:02 AM
- Liked Re: Computing Volatility Using Same Data for Different Time Periods (Intraday Data) for mkeintz. 03-05-2017 01:08 AM
- Liked Re: Computing Volatility Using Same Data for Different Time Periods (Intraday Data) for Ksharp. 03-05-2017 01:07 AM
- Liked Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? for Kurt_Bremser. 03-05-2017 12:45 AM
- Posted Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? on SAS Programming. 03-05-2017 12:44 AM
- Liked Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? for mkeintz. 03-05-2017 12:44 AM
- Liked Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? for rogerjdeangelis. 03-05-2017 12:44 AM
- Liked Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? for Ksharp. 03-05-2017 12:36 AM
- Posted Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? on SAS Programming. 03-04-2017 04:42 PM
- Posted Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? on SAS Programming. 03-04-2017 04:39 PM
- Posted Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? on SAS Programming. 03-04-2017 04:22 PM
- Liked Re: Proc SQL Taking a lot of Time. Am I doing anything wrong? for Kurt_Bremser. 03-04-2017 04:21 PM
-
Posts I Liked
Subject Likes Author Latest Post 2 1 2 1 1
04-29-2017
04:21 AM
Thanks Guys, there was a small error I had mistakenly put comma at the end of last select line
(select BS_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+60 ) as BSP60
and also the sec1 is recording the second number for the time interval hence, Sec1 = a.Sec1+'00:00:30't was replaced by Sec1=a.Sec1+30 and similarly for 60th second as well. The revised code is attached below for reference.
Libname Snap "F:\Wins Snap";
Libname MidQt "F:\MidQuote";
%Macro MidQuote1(d);
%Macro Midquote2(Stock);
Data MidQt.&Stock._MQ_&d; Set Snap.Snap_&Stock._&d;
Keep Sec1 BB_OrdPrice BS_OrdPrice;
run;
proc sort data=MidQt.&Stock._MQ_&d noduprecs;
by Sec1;
Run;
proc sql;
create table MidQt.&Stock._MQ_&d._Final as
select *,
(select BB_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+30 ) as BBP30,
(select BB_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+60 ) as BBP60,
(select BS_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+30 ) as BSP30,
(select BS_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+60 ) as BSP60
from MidQt.&Stock._MQ_&d as a;
quit;
%Mend Midquote2;
%Midquote2(ACC);
%Mend Midquote1;
%Midquote1(01032013);
... View more
04-29-2017
02:04 AM
Hello Friends, I am running the following code to get best buy and sell prices for 30th seconds and 1st minute.
Libname Snap "F:\Wins Snap";
Libname MidQt "F:\MidQuote";
%Macro MidQuote1(d);
%Macro Midquote2(Stock);
Data MidQt.&Stock._MQ_&d; Set Snap.Snap_&Stock._&d;
Keep Sec1 BB_OrdPrice BS_OrdPrice;
run;
proc sort data=MidQt.&Stock._MQ_&d noduprecs;
by Sec1;
Run;
proc sql;
create table MidQt.&Stock._MQ_&d._Final as
select *,
(select BB_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+'00:00:30't ) as BBP30,
(select BB_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+'00:01:00't ) as BBP60,
(select BS_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+'00:00:30't ) as BSP30,
(select BS_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+'00:01:00't ) as BSP60,
from MidQt.&Stock._MQ_&d as a;
quit;
%Mend Midquote2;
%Midquote2(ACC);
%Mend Midquote1;
%Midquote1(01032013);
After Running the code I am getting the following errors:
NOTE: Line generated by the invoked macro "MIDQUOTE2". 3 BS_OrdPrice from MidQt.&Stock._MQ_&d where Sec1=a.Sec1+'00:01:00't ) as BSP60, from 3 ! MidQt.&Stock._MQ_&d as a; quit; - 22 76 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.
ERROR 76-322: Syntax error, statement will be ignored.
NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds
NOTE: Line generated by the macro variable "D". 1 MidQt.ACC_MQ_01032013 ----- 22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.
Please help me in correcting it.
I have created it in macro as I have to run it for 50 stocks and 125 Days.
Thanks in Advance.
Ritesh
... View more
03-07-2017
11:30 PM
Hi @mkeintz,
Thanks for replying. I think this question has been reposted (allowed) by SAS Community moderator as it was marked spam for some reasons. Another variation of this question was posted earlier where you, @Ksharp, @Kurt_Bremser, & @Reeza had helped me to get the desired results.
Previous Question Link
Here is the solution code that I used based on suggestions....
%Macro MidQuote(T);
Data FD.&T._MQ; Set FD.&T._Final;
Keep DTM1 BBP BSP BBQ BSQ;
DT = Datepart(DTM1);
Format DT Date9.;
Dif = DTM1 - Lag(DTM1);
run;
proc sort data=FD.&T._MQ noduprecs;
by DTM1;
Run;
proc sql;
create table FD.&T._MQ_Final as
select *,
(select bbp from FD.&T._MQ where dtm1=a.dtm1+'00:01:00't ) as BBP1,
(select bbp from FD.&T._MQ where dtm1=a.dtm1+'00:05:00't ) as BBP5,
(select bsp from FD.&T._MQ where dtm1=a.dtm1+'00:01:00't ) as BSP1,
(select bsp from FD.&T._MQ where dtm1=a.dtm1+'00:05:00't ) as BSP5,
(select bbq from FD.&T._MQ where dtm1=a.dtm1+'00:01:00't ) as BBQ1,
(select bbq from FD.&T._MQ where dtm1=a.dtm1+'00:05:00't ) as BBQ5,
(select bsq from FD.&T._MQ where dtm1=a.dtm1+'00:01:00't ) as BSQ1,
(select bsq from FD.&T._MQ where dtm1=a.dtm1+'00:05:00't ) as BSQ5
from FD.&T._MQ as a;
quit;
%Mend;
%Midquote(ACC);
Thanks a ton @mkeintz for taking out time and helping.
Love to be a part of the SAS Communites. Have a great day ahead.
Ritesh
... View more
03-05-2017
12:44 AM
This one is awesome it takes less than 1 second to run and get the desired results of HI & LO . But it is not counting all the frequencies between that 5 minute & 1 minute time interval. It is showing Count5 is only 14 for the 5 minute time interval.
... View more
03-04-2017
04:42 PM
335 ACC_New (in=1 drop=time1 time5) - 24 24 This one referred to the number 1 in the code.
... View more
03-04-2017
04:39 PM
Hi @Kurt_Bremser, Thanks a ton for the help.
proc sort data=DA.ACC_Final;
by dtm1;
run;
Data ACC_New; Set DA.ACC_Final;
run;
data ACC_Test;
set DA.ACC_Final (keep=dtm1 tradeprice);
by dtm1;
retain count max_price min_price;
if first.dtm1
then do;
count = 0;
max_price = 0;
min_price = 99999999999;
end;
count + 1;
max_price = max(tradeprice,max_price);
min_price = min(tradeprice,min_price);
if last.dtm1
then do;
time1 = intnx('minute',dtm1,-1);
time5 = intnx('minute',dtm1,-5);
output;
end;
drop tradeprice;
run;
data ACC_HILO;
merge
ACC_New (in=1 drop=time1 time5)
ACC_New (drop=dtm1 time5 rename=(time1=dtm1 count=count1 max_price=max_price1 min_price=min_price1))
ACC_New (drop=dtm1 time1 rename=(time5=dtm1 count=count5 max_price=max_price5 min_price=min_price5))
;
by dtm1;
if a;
run;
Log:
303 304 proc sort data=DA.ACC_Final; 305 by dtm1; 306 run;
NOTE: Input data set is already sorted, no sorting done. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
307 308 Data ACC_New; Set DA.ACC_Final; 309 run;
NOTE: There were 708045 observations read from the data set DA.ACC_FINAL. NOTE: The data set WORK.ACC_NEW has 708045 observations and 47 variables. NOTE: DATA statement used (Total process time): real time 0.27 seconds cpu time 0.26 seconds
310 311 data ACC_Test; 312 set DA.ACC_Final (keep=dtm1 tradeprice); 313 by dtm1; 314 retain count max_price min_price; 315 if first.dtm1 316 then do; 317 count = 0; 318 max_price = 0; 319 min_price = 99999999999; 320 end; 321 count + 1; 322 max_price = max(tradeprice,max_price); 323 min_price = min(tradeprice,min_price); 324 if last.dtm1 325 then do; 326 time1 = intnx('minute',dtm1,-1); 327 time5 = intnx('minute',dtm1,-5); 328 output; 329 end; 330 drop tradeprice; 331 run;
NOTE: There were 708045 observations read from the data set DA.ACC_FINAL. NOTE: The data set WORK.ACC_TEST has 23211 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.17 seconds cpu time 0.17 seconds
332 333 data ACC_HILO; 334 merge 335 ACC_New (in=1 drop=time1 time5) - 24 24 ERROR 24-322: Variable name is not valid.
336 ACC_New (drop=dtm1 time5 rename=(time1=dtm1 count=count1 max_price=max_price1 336! min_price=min_price1)) 337 ACC_New (drop=dtm1 time1 rename=(time5=dtm1 count=count5 max_price=max_price5 337! min_price=min_price5)) 338 ; ERROR: Variable time1 is not on file WORK.ACC_NEW. ERROR: Variable count is not on file WORK.ACC_NEW. ERROR: Variable max_price is not on file WORK.ACC_NEW. ERROR: Variable min_price is not on file WORK.ACC_NEW. ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.ACC_NEW. ERROR: Variable time5 is not on file WORK.ACC_NEW. ERROR: Variable count is not on file WORK.ACC_NEW. ERROR: Variable max_price is not on file WORK.ACC_NEW. ERROR: Variable min_price is not on file WORK.ACC_NEW. ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.ACC_NEW. 339 by dtm1; 340 if a; 341 run;
NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.ACC_HILO may be incomplete. When this step was stopped there were 0 observations and 1 variables. WARNING: Data set WORK.ACC_HILO was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.03 seconds
... View more
03-04-2017
04:22 PM
This one is pretty quick. I got few errors in the last step while merging. 294 data ACC_HILO; 295 merge 296 ACC_New (in=1 drop=time1 time5) - 24 24 ERROR 24-322: Variable name is not valid. 297 ACC_New (drop=dtm1 time5 rename=(time1=dtm1 count=count1 max_price=max_price1 297! min_price=min_price1)) 298 ACC_New (drop=dtm1 time1 rename=(time5=dtm1 count=count5 max_price=max_price5 298! min_price=min_price5)) 299 ; ERROR: Variable time1 is not on file WORK.ACC_NEW. ERROR: Variable count is not on file WORK.ACC_NEW. ERROR: Variable max_price is not on file WORK.ACC_NEW. ERROR: Variable min_price is not on file WORK.ACC_NEW. ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.ACC_NEW. ERROR: Variable time5 is not on file WORK.ACC_NEW. ERROR: Variable count is not on file WORK.ACC_NEW. ERROR: Variable max_price is not on file WORK.ACC_NEW. ERROR: Variable min_price is not on file WORK.ACC_NEW. ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.ACC_NEW. 300 by dtm1; 301 if a; 302 run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.ACC_HILO may be incomplete. When this step was stopped there were 0 observations and 1 variables. WARNING: Data set WORK.ACC_HILO was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.03 seconds
... View more
03-04-2017
03:26 PM
HI Reeza, Proc Expand didn't work for me, I do have ETS Installed. It would be great if you could help / guide me with Proc Expand Code. Really appreciate your help. Thanks Ritesh
... View more
03-04-2017
03:02 PM
HI @Kurt_Bremser;
Attaching the Txt file for Datalines statement as well.
Thanks.
Ritesh
... View more
03-04-2017
02:44 PM
Hi @Kurt_Bremser,
Attaching the SAS Dataset Pic as Unable to attach the data.
... View more
03-04-2017
02:35 PM
Hi @Kurt_Bremser,
Thanks for responding. It would be great if you can give me some hint about how to go about building the max, min and count values per smallest time unit, sort by time unit, and calculate the -1 and -5 minute times.
This is how my dataset looks like:
DTM1 TradePrice
6-3-13 9:15
1218
6-3-13 9:15
1218.05
6-3-13 9:15
1219.55
6-3-13 9:15
1219.6
6-3-13 9:15
1219.7
6-3-13 9:15
1219.7
6-3-13 9:15
1218
6-3-13 9:15
1218
6-3-13 9:15
1218
6-3-13 9:15
1218
6-3-13 9:15
1218
6-3-13 9:16
1219.25
6-3-13 9:16
1218.05
6-3-13 9:16
1218.05
6-3-13 9:16
1218
6-3-13 9:16
1219
6-3-13 9:16
1219
6-3-13 9:16
1219
6-3-13 9:16
1219.3
6-3-13 9:16
1218.8
6-3-13 9:16
1218.25
6-3-13 9:16
1218.2
6-3-13 9:17
1218
6-3-13 9:17
1218
6-3-13 9:17
1218.5
6-3-13 9:17
1218.55
6-3-13 9:17
1218.8
Thanks in advance.
Ritesh
... View more
03-04-2017
11:59 AM
I am running the below code for a data-set containing 708045 Observations for 2 variables. And Its Taking approx 41 minutes to run.
Is it reasonable? Am I doing something wrong?
%Macro HILO(T);
Data &T._ID; Set DA.&T._Final;
Keep DTM1 TradePrice;
run;
proc sql;
create table &T.HILO as
select *,
(select Max(TradePrice) from &T._ID where dtm1=a.dtm1+'00:01:00't ) as HI1,
(select Max(TradePrice) from &T._ID where dtm1=a.dtm1+'00:05:00't ) as HI5,
(select Min(TradePrice) from &T._ID where dtm1=a.dtm1+'00:01:00't ) as LO1,
(select Min(TradePrice) from &T._ID where dtm1=a.dtm1+'00:05:00't ) as LO5,
(select Count(*)from &T._ID where dtm1=a.dtm1+'00:01:00't ) as Count1,
(select Count(*)from &T._ID where a.dtm1 <= dtm1 <=a.dtm1+'00:05:00't ) as Count5
from &T._ID as a;
quit;
Data DA.&T.HILO; Set &T.HILO;
PVol_1min = SQRT(1/Count1*(log(Hi1/Lo1)**2));
PVol_5min = SQRT(1/Count5*(log(Hi5/Lo5)**2));
run;
Data Analysis.&T._Final;
Merge DA.&T.HILO DA.&T._Final;
By DTM1;
run;
proc datasets lib=work
nolist kill;
quit;
run;
%Mend;
%HILO(ACC);
... View more
03-04-2017
10:32 AM
I am running the above code for a data-set containing 708045 Observations for 2 variables. And Its Taking approx 41 minutes to run. Is it reasonable? Am I doing something wrong?
... View more
03-04-2017
08:27 AM
HI @Reeza, Yes I am using my last solutions as well for other cases. In the last solution, we had a single observation for each time periods. This time we have multiple ones. And also we need to transform the price variables into return variables. Here I am stuck also because I am novice at SQL though, I can edit the basic codes as per requirement. For Example: There is another measure of Volatility Parkinson's 1980 (Hi-Lo) Measure. And I thought of using the same if this doesn't work out. I have expanded the earlier code as below: proc sql; create table ACC as select *, (select Max(TradePrice) from ACC_ID where dtm1=a.dtm1+'00:01:00't ) as HI1, (select Max(TradePrice) from ACC_ID where dtm1=a.dtm1+'00:05:00't ) as HI5, (select Min(TradePrice) from ACC_ID where dtm1=a.dtm1+'00:01:00't ) as LO1, (select Min(TradePrice) from ACC_ID where dtm1=a.dtm1+'00:05:00't ) as LO5, (select Count(TradePrice)from ACC_ID where dtm1=a.dtm1+'00:01:00't ) as Count1, (select Count(TradePrice)from ACC_ID where dtm1=a.dtm1+'00:05:00't ) as Count5 from ACC_ID as a; quit; Data ACC; Set ACC; PVol_1min = SQRT(1/_Freq_*(log(Hi1/Lo1)**2)); PVol_5min = SQRT(1/_Freq_*(log(Hi5/Lo5)**2)); run;
... View more
03-04-2017
07:40 AM
HI @Reeza,
Either the first Price of that second or the average price of that second can be taken.
Thanks
Ritesh
... View more