Proc SQL Error

rkdubey84 — Sat, 29 Apr 2017 06:04:34 GMT

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

Re: Proc SQL Error

rkdubey84 — Sat, 29 Apr 2017 08:21:48 GMT

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);

topic Re: Proc SQL Error in SAS Programming

Proc SQL Error

Re: Proc SQL Error