SAS data step view and data wrap in a macro for lo

问题:

For a university research project I am retrieving data from WRDS via SAS and am relatively new to SAS. I am trying to retrieve data in a specific interval provided by WRDS which actually works very for me; the structure is as follows

[1]Define some macro variable
[2]Use data step view
[3]Make manipulation on data
[4]Export the data to csv

In particular I am retrieving data for a stock for every single year. Instead of changing the variable all the time a macro that would allow me to provide the years as input would be the "most elegant" solution (inspired from here: [SAS Loop through list of macro variable][1]). However, my macro doesn't work as expected (I also changed the structure a bit adding an append step instead of exporting to CSV).

Step [3] is now reporting error: ERROR 180-322: Statement is not valid or it is used out of proper order.

I am putting the code here (part 3 I leave as it is because it's creating problems, the other I have shortened a bit (I commented):

%macro get_stock_ts(list_years);

%local i tables;
%do i=1 %to %sysfunc(countw(&list_years,%str( )));
%let tables=%scan(&list_years,&i,%str( ));

proc datasets lib = work memtype = all nolist;
    delete _:;
quit; 

%local stock = "COP";       

%local taq_ds=taq.&tables:; 
%local filename = &tables._&stock; 

data  _v_&tables / view=_v_&tables;
  set &taq_ds;  
  where symbol = &stock and                             
        (time between '9:30:00't and '16:00:00't) and       
        mode = 12 and                                   
        EX = 'N';                                       
run; 

data xtemp2; 
 set _v_&tables; 
 by symbol date time; 
 format itime rtime time12.; 
 if first.symbol = 1 or first.date = 1 then do;         
    rtime = time; 
    iprice = bid; 
    oprice = ofr; 
    itime = &start_time; 
 end; 

 if time >= itime then do;                                              
       output;                                                          
       itime = itime + &interval_seconds; 
       do while(time >= itime);                                         
           output; 
           itime = itime + &interval_seconds; 
       end; 
end; 
rtime = time; 
iprice = bid; 
oprice = ofr; 
retain itime iprice oprice;                                             
run; 

proc append base = all data = work.xtemp2 force;
run;

proc printto log="/home/Logs/ &filename.log" new; run;
proc printto log=log; run;                                          

%end;
%mend get_stock_ts;

and then I invoke for instance:

%get_stock_ts(cq_2009)

Would you know how it comes that running the code as standalone works fine, but as soon as I wrap it as a macro the "good" starts to create problems?

edit: I have adjusted the code above and getting the following. Is it because macro and local macro variables have a problem with the statement?

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         %get_stock_ts(cq_2009)
 ERROR: Invalid symbolic variable name =.
 ERROR: Invalid symbolic variable name "COP".
 ERROR: Invalid symbolic variable name =.
 ERROR: Symbolic variable name TAQ.CQ_2009 must contain only letters,     digits, and underscores.
 ERROR: Invalid symbolic variable name TAQ.CQ_2009.
 ERROR: Invalid symbolic variable name :.
 ERROR: Invalid symbolic variable name =.
 ERROR: Invalid symbolic variable name =.
 ERROR: Invalid symbolic variable name '9:30:00'T.
 ERROR: Invalid symbolic variable name =.
 ERROR: Invalid symbolic variable name 1.
 ERROR: Invalid symbolic variable name *.
 ERROR: Invalid symbolic variable name 60.
 ERROR: Variable symbol is not on file WORK.ALL.

 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: DATA statement used (Total process time):
   real time           0.00 seconds
   cpu time            0.00 seconds

 ERROR: Variable SYMBOL not found.
 ERROR: Variable DATE not found.
 ERROR: Variable TIME not found.
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SORT used (Total process time):
   real time           0.00 seconds
   cpu time            0.00 seconds

 ERROR: File WORK.XTEMP2.DATA does not exist.

回答1:

No idea what your error is but these lines in your macro look problematic.

First:

%symdel stock taq_ds filename start_time interval_seconds; */
%let stock = "COP";     
%let taq_ds=taq.&tables:;   
%let filename = &tables._&stock; 
data  _v_&tables / view=_v_&tables;

What is the purpose of the %SYMDEL? You probably just want to make some %LOCAL macro variables instead. They will then disappear when the macro ends. So no need to delete them. If you need to set them to empty, so inside a %do loop, then just use %let statements.

Also do you really want to start a statement style comment at the end of the first line? Since the next three lines are all macro statements I think the * will comment up to the semi-colon at the end of the data statement.

Second:

DM 'log; file "/home/ &filename.log" replace'; 
DM "log; clear; ";                                          

Why are you using DM commands? That can only work if you are actually still running SAS using Display Manager.

What are you trying to do here? If you want to write the log to a separate location then redirect BEFORE hand using

proc printto log="filename" new; run;

then close it afterwards.

proc printto log=log; run;

回答2:

This line is just wrong.

%local stock = "COP";   

You are trying the define local macro variables named = and "COP". You probably meant to do this.

%local stock ;
%let stock = "COP";   

回答3:

I am using SAS Studio working on the WRDS cloud. I have realized the following:

When I run my code - not as a macro - "block_by-block", then everything works very fine. If I run the entire script, I get an error in the data xtemp2 part:

 73          '9:30:00't and '16:00:00't) and     mode = 12 and            EX =
 73       ! 'N';           run;   *Screen data to find the trade before a set
 73       ! time interval   data xtemp2;       set _v_&tables;       by symbol
                                               ___
                                               180
 73       ! date time;       format itime rtime time12.;       if
 ERROR 180-322: Statement is not valid or it is used out of proper order.

But if I run my code once block-by-block, afterwards I can run the entire script without any issues.

So I think the solution is to run the code blocks sequentially. Is there a possibility to "simulate" a sequential run within the code?

edit: I tried to use the sleep function to pause the code there but that still doesn't work, it gives me the error:

 129        data xtemp2;
 130             set _v_&tables;
 130             set _v_&tables;
                 ___
                 180
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 .
 .
 .

v&tables comes from my data step view.

edit: I tried the sleep function, however, it doesnt work. Funnily, if I run everything until data step view and then run everything from the actual data step the code words; and after that I can re-run the FULL code instead of splitting by two times.

I will create a new question which links back to this and the above mentioned question where I wrap the code into a macro...

SOLUTION FOUND: I had to add extra "run;" statements before the data steps and then it works (credits to Richard who found this).