SAS: sort error (by variable not sorted properly)

Question

This question is a follow up from another I had here SAS: Data step view -> error: by variable not sorted properly; I am opening a new question as the desired solution is slightly different: As I am looping through several input files, one of the raw-files is not properly sorted, I wonder what I could do to make my program to skip that particular input file and just continue?

Quote:

I am using a macro to loop through files based on names and extract data which works fine for the majority of the cases, however from time to time I experience

ERROR: BY variables are not properly sorted on data set CQ.CQM_20141113.

where CQM_20141113 is the file I am extracting data from. In fact my macro loops through CQ.CQM_2014: and it works up until 20141113. Because of this single failure the file is then not created.

I am using a data step view to "initialize" the data and then in a further step to call data step view (code sample with shortened where conditions):

%let taq_ds = cq.cqm_2014:;

data _v_&tables / view=_v_&tables;
     set &taq_ds;
     by sym_root date time_m; *<= added by statement
     format sym_root date time_m;
     where sym_root = &stock;   
run; 

data xtemp2_&stockfiname (keep = sym_root year date iprice);
     retain sym_root year date iprice; 
     set _v_&tables; 
     by sym_root date time_m;

/* some conditions */
run;

When I see the error via the log file and I run the file again, then it works (sometimes I need a few trials).

I was thinking of a proc sort, but how to do that when using data step view? Please note the cqm-files are very large (which could also be the root of the problem). End Quote:

edit: I tried your code (and deleted the by statement in the data step view), however I am getting this error:

NOTE: Line generated by the macro variable "TAQ_DS".
152         cq.cqm_2013:
                       _
                       22
                       200
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', 
              ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, 
              INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, 
              UNION, USING, WHERE.  

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of 
      statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds



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

NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. 
      This might cause NOTE: No observations in data set.
WARNING: The data set WORK.XTEMP2_OXY may be incomplete.  When this step was 
         stopped there were 0 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds

DomPazz DomPazz · Accepted Answer · 2018-04-30T18:47:58

Do you need the by statement in the view creation?

If not, then sort from the view into a temporary data set:

proc sort data=_v_&tables out=__temp; 
     by sym_root date time_m;
run;

data xtemp2_&stockfiname (keep = sym_root year date iprice);
     retain sym_root year date iprice; 
     set __temp; 
     by sym_root date time_m;

/* some conditions */
run;

Another option would be to create the view in PROC SQL adding the sort order:

proc sql noprint;
create view _v_&tables as
select <whatever>
   from &taq_ds
   where <clause>
   order by sym_root, date, time_m;
quit;

SAS: sort error (by variable not sorted properly)

1 Answers