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
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);
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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.