Is there a way to not have a proc error out when there are no observations?

[DKidd]

So, I have this mainframe job, that I received help with from this site (thanks again), and it works great!  Except today when there were no observations found.

Essentially what happens is that I have a dataset, with a bunch of different types of transactions.

//STEP1    EXEC SAS9,TIME=120,OPTIONS='LS=133,NODATE,MACRO'             
 //RG1  DD DSN=IN.DATASET,DISP=SHR          
//OUT  DD DSN=OUT.DATASET,DISP=SHR             
//SAS.SASLIST DD DSN=OUT.REPORT,DISP=SHR   
//SYSIN DD *                                                           
%GLOBAL DAT;                                                           
OPTIONS DQUOTE MISSING=' ' LINESIZE=133 NONUMBER;                      
DATA RGDATA;                                                           
  INFILE RG1;                                                          

           FIELD1

           FIELD2

.

.

So I strip off the records that I don't want and then I do a proc sort

PROC SORT DATA=RGDATA;
  BY FIELD1 FIELD2;

and then a Proc Print followed by a Proc Tabulate

PROC TABULATE FORMAT=DOLLAR12.2 OUT=DAT1 (DROP=_:);  
BY FIELD1;                                           
CLASS FIELD2;     

Then I follow the Proc Tabulate with another Proc Sort.         

PROC SORT DATA=DAT1;
  BY FIELD1 FIELD2;
DATA _NULL_;        
  FILE OUT DSD;     
  SET DAT1;         
  PUT (_ALL_) (+0);          

 When I look at the errors it says

106        PROC SORT DATA=DAT1;   
107          BY FIELD1 FIELD2;  
ERROR: VARIABLE FIELD1 NOT FOUND.   

ERROR: VARIABLE FIELD2 NOT FOUND.

Everything I've tried just gives me more errors. So any help would be greatly appreciated.

Any suggestions?

thanks.       

[ballardw]

You might show how you filter your records so we have a likely place in your code to reference things.

What do you want to actually happen when you get that no records condition? Stop the job, skip to another part of the job not dependent on that data subset, something else?

Also is this a batch job or something interactive? Some of the likely approaches have different behaviors.

[DKidd]

This is a batch job.

Essentially if FIELD1 = FIELD2 on the record, it gets deleted.  My users are only concerned with information on records where they don't equal.

The PROC SORT where the error occurs is the last part of that SAS step.

The datasets that are created in that step are emailed later as a .csv file and a .txt file.

At a minimum, I just would like the step to continue as if things were normal.  That it's okay to not have any observations.

Ideally it would be nice to put 'no records found' in the file that will be sent as a .txt, and something along the same lines for the other file.  But that can always be done with another utility if I need to.

Does that help any?

[ballardw]

This is one of the things the macro language is designed for. In your code you would

1) remove the code you currently have with the proc sort and the Data _null_ step

2) replace with the code below. If you have not used macros the bit betwee %Macro and %mend; only defines macro code. The line with %IsItMissing actually executes the macro. If you start attempting to write macros it is a very good idea to always explicitly end procedures and data steps with a run statement. Since macros create code to execute and may be creating parts of a procedure conditionally if you aren't careful you can create instances where your code misbehaves.

/*Define a macro to check if a data set has records
  and if so send them to the file
  if no records in the input then write message
  to the file
*/
%macro IsItMissing;
   /* test if data set dat1 has any records by assigning 
       a count of records into a macro variable*/
   proc sql noprint;
      select count(*) into : RecordCount
      from dat1;
   quit;
   /* test the value of the macro variable if >0 then there is at least one record 
      for output*/ 
   %if &RecordCount > 0 %then %do;
      PROC SORT DATA=DAT1; 
        BY FIELD1 FIELD2;
      run;
      DATA _NULL_;         
        FILE OUT DSD;      
        SET DAT1;          
        PUT (_ALL_) (+0);
      run; 
   %end;
   %else %do;
      /* the record count is 0 then indicate that in the text file*/
      DATA _NULL_;         
        FILE OUT DSD;      
        PUT "No records Found";
      run;
   %end;
%mend;
/*Use the macro*/
%IsItMissing

[Tom · Accepted solution]

As @Ron_MacroMaven said you can quickly make the PROC SORT step conditional on the previous step producing a non-empty dataset by using IFC() function and the SYSNOBS automatic macro variable.

Try this little example with and without and actual data lines after the CARDS statement.

data RGDATA;
 input field1 - field3 ;
cards;
1 2 3
;
%put &=sysnobs ;

PROC TABULATE FORMAT=DOLLAR12.2 OUT=DAT1 ;
 BY FIELD1;                                            
 CLASS FIELD2;     
 table field2 ;
run ;
%put &=sysnobs ;

%sysfunc(ifc(&sysnobs>0,%nrstr(
PROC SORT DATA=DAT1; 
  BY FIELD1 FIELD2;
run;
),));

PS You shoudl always include RUN or QUIT statement (which ever is appropriate) at the end of your steps.  SAS is smart enought to know your step has ended when it sees the next one starting, but human editors of your code are not that smart.

It is not hard to write a line when there is nothing there. You could even write it into the same file you are writing the data, but you probably don't want to as that might confuse those that are using that file.

DATA _NULL_;         
  FILE OUT DSD;      
  if _n_=1 and _eof then put 'No data to report';
  SET DAT1 end=_eof;          
  PUT (_ALL_) (+0);          
run;

[Ron_MacroMaven]

this is a friendly rejoineder to @Tom

1. a comparison test

(&sysnobs gt 0)

(&sysnobs ge 1)

 is not necessary for a logical condition

the global macro variable sysnobs has values in (0,1:&sysmaxlong)

(RJF 2017-09-05 correction

... in (-1,1,<maximum nobs>) ) where -1 means previous data step had nobs=0

this comment.1 about comparison tests being unnecessary  is generally true,

but is necessary here because of the possible value of -1

)

zero is false, all other values, except missing, are true

True is not False: Evaluating Logical Expressions 

2. *-always-* end a step with a run or quit?

my style guide says

* always end an %include (routine or subroutine)

and macro (process or procedure, not function)

with a run; statement

at any step where you want the system macro variables updated

then run is required, otherwise it is extraneous.

Ron Fehd  proofreader

[Tom]

---

@Ron_Fehd_macro_maven wrote:

this is a friendly rejoineder to @Tom

1. a comparison test

(&sysnobs gt 0)

(&sysnobs ge 1)

 is not necessary for a logical condition

t

he global macro variable sysnobs has values in (0,1:&sysmaxlong)

zero is false, all other values, except missing, are true

True is not False: Evaluating Logical Expressions 

... 

Ron Fehd  proofreader

---

@Ron_MacroMaven 

The SYSNOBS macro variable can also take the value -1 which would be treated as TRUE as a boolean expresssion since it is not zero.  Try running the PROC TABULATE step with an empty input dataset and see what value it sets the SYSNOBS macro varaible to.

2516  PROC TABULATE data=sashelp.class(obs=0) OUT=DAT2 ;
2517   CLASS sex;
2518   table sex ;
2519  run ;

NOTE: No observations in data set SASHELP.CLASS.
NOTE: The data set WORK.DAT2 has 0 observations and 0 variables.
NOTE: PROCEDURE TABULATE used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


2520  %put &=sysnobs ;
SYSNOBS=-1

[Ron_MacroMaven]

<sigh> every day: learn one more esoterica of SAS software

thanks @Tom

[Ron_MacroMaven]

yes, a macro is the first solution that comes to mind for branching code.

The same conditional logic can be placed inline with sysfunc + ifc.

%sysfunc(ifc(<conditional expression>
            ,%nrstr(%put expression is true;)
            ,%nrstr(%put expression is false;)
        )   )

your conditional expression is &sysnobs from the step that produces the empty dataset

*produce possible empty data step;

run;*updates sysnobs;
%put echo &=sysnobs;

%sysfunc(ifc(
            ,%nrstr(*obs gt zero;)
            ,%nrstr(*obs eq zero;)
        )   )

better than macro? no, just another method.

Ron Fehd  more than one way... maven

[Tom]

What is the PROC TABULATE step doing for you?  There are other ways to summarize data (proc sql, proc summary) that probably would not end up making a dataset with zero variables.  Perhaps you can just replace that step.

[DKidd]

So I apologize for not getting back yesterday, since Monday was a holiday.

As for why I'm using PROC TABULATE, I have to total the amount of a certain fee for each county that collected that fee, under the circumstances that I edit for at the beginning.  That's why I use PROC Tabulate.  But, I will admit that the only training I have in SAS is on-the-job training, self teaching, and trying to figure out other batch jobs that use SAS.  PROC Tabulate is the one used most often and so I know more about that proc.

Also, I tried the macro and got the error "Table work.dat1 doesn't have any columns. Proc SQL requires each of its tables to have at least 1 column.

Then it had some other errors, but I can't tell if it's because of the PROC SQL having its error or not.

So then I tried it the other example, and changed the job to look like this:

PROC TABULATE FORMAT=DOLLAR12.2 OUT=DAT1 (DROP=_:);

.

.

RUN;

%PUT &=SYSNOBS ;                    
%SYSFUNC(IFC(&SYSNOBS > 0,%NRSTR(   
PROC SORT DATA=DAT1;                
  BY Field1 Field2;               
RUN;                                
  ),));                             
DATA _NULL_;                        
  FILE OUT DSD;                     
  SET DAT1;                         
  PUT (_ALL_) (+0);                 

Then I got this:

108        %PUT &=SYSNOBS ;                                            
&=SYSNOBS                                                              
109        %SYSFUNC(IFC(&SYSNOBS > 0,%NRSTR(                           
WARNING: APPARENT SYMBOLIC REFERENCE SYSNOBS NOT RESOLVED.             
110        PROC SORT DATA=DAT1;                                        
111          BY Field1 Field2;                                       
112        RUN;                                                        
113          ),));                                                     
WARNING: APPARENT SYMBOLIC REFERENCE SYSNOBS NOT RESOLVED.             
ERROR: A CHARACTER OPERAND WAS FOUND IN THE %EVAL FUNCTION OR %IF CONDITION WHERE A NUMERIC OPERAND IS REQUIRED. THE CONDITION WAS:
       &SYSNOBS > 0                                                     
ERROR: ARGUMENT 1 TO FUNCTION IFC REFERENCED BY THE %SYSFUNC OR %QSYSFUN MACRO FUNCTION IS NOT A NUMBER.
ERROR: INVALID ARGUMENTS DETECTED IN %SYSCALL, %SYSFUNC, OR %QSYSFUNC ARGUMENT LIST.  EXECUTION OF %SYSCALL STATEMENT OR %SYSFUNC OR %QSYSFUNC FUNCTION REFERENCE IS TERMINATED.

So, there has to be a problem child in every group, I just happen to be the one today.

I apologize for being a pain to you guys, but I really do appreciate the help. 

I have a feeling that it's something obvious, but with my lack of in-depth knowledge of SAS, I'm not seeing it.  I've tried different things to resolve the reference error, but nothing that I try seems to work.  😞

[Tom]

You must be running an old version of SAS if SYSNOBS is not found and &=SYSNOBS syntax doesn't work?

If you want to sum a varaible use PROC SUMMARY.  If the input is empty then PROC SUMMARY will properly generate an empty dataset but one that has the proper variables defined, unlike PROC TABULATE. PROC TABULATE is really for making reports, not summarizing data.

So if your dataset that you want to summarize is called HAVE and the variable you want to sum is called FIELD2 and the variable that you want separate sums for each value of is FIELD1 then you could use this code.  

proc summary data=have nway ;
  class field1 ;
  var field2 ;
  output out=dat1 sum= ;
run;

data _null_;
  file out dsd ;
  set dat1 ;
  by field1 ;
  put field1 field2 ;
run;

The BY FIELD1 is not needed in the data _null_ step, but if your requirement is that the resulting CSV file have the values sorted by FIELD1 then adding that BY statement will make it so that SAS will generate an error if for some reason DAT1 was NOT sorted by FIELD1.

[DKidd]

Thank you for that information.

I just barely added sysnobs to my %GLOBAL statement that I already had and then I got a condition code of zero.

But, I will try your suggestion for using the PROC SUMMARY and see if it adversely effects the rest of my job or if it works just fine.  At least I have a work around for the empty dataset, and I learned a little bit more than I did last week.

Thanks, everyone, for taking the time to help me out and giving me your suggestions.

It's sad that SAS has all kinds of things available to use.  We have it here at work, but nobody really knows how to use it.  Just bits and pieces of it.