DATA Step, Macro, Functions and more

Proc SQL Error

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Proc SQL Error

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


Accepted Solutions
Solution
‎04-29-2017 04:22 AM
Contributor
Posts: 36

Re: Proc SQL Error

Posted in reply to rkdubey84

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 solution in original post


All Replies
Solution
‎04-29-2017 04:22 AM
Contributor
Posts: 36

Re: Proc SQL Error

Posted in reply to rkdubey84

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);
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 138 views
  • 0 likes
  • 1 in conversation