topic Re: Checking nobs for an empty dataset in SAS Programming

Checking nobs for an empty dataset

kz_ — Fri, 28 Jan 2022 18:25:34 GMT

I am reading this blog post:

https://blogs.sas.com/content/sgf/2021/03/24/how-to-conditionally-stop-sas-code-execution-and-gracefully-terminate-sas-session/

I copied the code directly from the blog (changed the dataset name to my data), but it doesn't work. Can anyone tell me why? The error in the log is:

ERROR: Invalid sequence of commands for file WORK.CLAIMS.DATA.

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

data _null_;
   if 0 then set claims nobs=n;
   if n=0 then
   do;
      put 'NOTE: No suspicious cases were found. Further processing is terminated.';
      call execute('endsas;');
   end;
   stop;
run;

The whole reason I'm reading this blog is that I want to check if a dataset read in from a proc iml step has zero observations. If so, I want to send an email to myself, and if not, I want to continue the program. So any recommendations on a nice way to do that would be appreciated.

Re: Checking nobs for an empty dataset

Tom — Fri, 28 Jan 2022 19:58:44 GMT

Please share the actual log lines that generate that error. It almost sounds like there is an issue with the CLAIMS dataset itself and not with your program.

Note that running ENDSAS will not work well if you are using SAS/Studio, Enterprise Guide or some other tool that runs SAS in the background for you. It will literal end the SAS session and the front-end program that send that code to SAS will not be able to show you the LOG or anything else.

Re: Checking nobs for an empty dataset

sbxkoenk — Fri, 28 Jan 2022 20:08:56 GMT

Hello,

I do not see an ERROR in your code. Show us the LOG!
I also check the number of observations in a data set that way.
It is a very performant | quick check as it only checks the header portion of the data set (containing the metadata). No record is read!

data work.claims;
length record $ 250;
if 0;
run;

data _NULL_;
 if 0 then set work.claims nobs=count;
 call symputx('numobs',put(count,8.));
 STOP;
run;
%PUT &=numobs;

See also here for the e-mail part of your question :

How to send email using SAS?
https://communities.sas.com/t5/SAS-Communities-Library/How-to-send-email-using-SAS/ta-p/746523

Cheers,

Koen

Re: Checking nobs for an empty dataset

kz_ — Fri, 28 Jan 2022 23:33:27 GMT

Claims is a dataset with all desired variables but no observations. Here are all of the log lines after deleting the endsas statement (log is the same either way):

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

69 data _null_;

70 if 0 then set claims nobs=n;

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

71 if n=0 then

72 do;

73 put 'NOTE: No suspicious cases were found. Further processing is terminated.';

75 end;

76 stop;

77 run;

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

79 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: Checking nobs for an empty dataset

kz_ — Fri, 28 Jan 2022 23:34:43 GMT

FYI, I intend to include endsas for when the program is run automatically once a month - that is why I want an email when there are zero observations.

Re: Checking nobs for an empty dataset

kz_ — Fri, 28 Jan 2022 23:39:06 GMT

Mostly I was just curious about why the code above doesn't work. I assume it should be correct since it's from a SAS blog post. Anyway, I got my code to work using proc sql count:

proc sql; 
select count(*) into :n_claims from claims; 
quit;

%if &n_claims = 0 %then %do; 
[things to do];
%end;

Re: Checking nobs for an empty dataset

kz_ — Fri, 28 Jan 2022 23:51:30 GMT

Maybe something is missing here. The first data step erases all the observations when there are observations, and the second one produces the same error as the original code (if I don't run the first data step).

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

69 data _NULL_;

70 if 0 then set work.claims nobs=count;

71 call symputx('numobs',put(count,8.));

72 STOP;

73 run;

ERROR: Invalid sequence of commands for file WORK.CLAIMS.DATA.

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

WARNING: Apparent symbolic reference NUMOBS not resolved.

74 %PUT &=numobs;

numobs

77 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: Checking nobs for an empty dataset

Tom — Sat, 29 Jan 2022 02:15:33 GMT

You have posted two contradictory logs.

The previous one is showing an error because this is no WORK.CLAIMS dataset to set.

This one is showing a strange error that seems to indicate WORK.CLAIMS exists, but is not a valid dataset. Or perhaps it is a VIEW that generates an error?

In general to fix some of these things the simple idea is to set your macro variable to whatever your FAIL condition is. Then do the test and if it does not change the value that you still ahve something to test.

%let nobs=0;
data _null_;
  call symputx('nobs',nobs);
  stop;
  set claims nobs=nobs;
run;

If if you are running a recent enough version of SAS use some open code %IF %THEN %DO END.

%if %sysfunc(exist(work.claims)) %then %do;
data _null_;
  call symputx('nobs',nobs);
  stop;
  set claims nobs=nobs;
run;
%end;
%else %do;
%let nobs=0;
%end;

Re: Checking nobs for an empty dataset

Patrick — Sat, 29 Jan 2022 02:25:18 GMT

@kz_

Your initial code works for me.

A select count(*) will also work but needs to travers through the whole table which for a big table will impact on performance. You can for SAS tables query the dictionary tables instead. This doesn't require processing the data and though performance is independent of table size.

%let n_claims=;
proc sql; 
  select nlobs into :n_claims 
  from dictionary.tables 
  where libname='WORK' and memname='CLAIMS';
quit;

%if &n_claims = 0 %then %do; 
[things to do];
%end;

I'm querying in above code NLOBS and not NOBS. NLOBS will return the number of non-deleted observations in a SAS table.

If deleting rows in a table using Proc SQL Delete then rows get only logically deleted and NOBS would return a "wrong" count.

Re: Checking nobs for an empty dataset

sbxkoenk — Sat, 29 Jan 2022 10:49:02 GMT

Hello @kz_ ,

I erase all observations on purpose (to have zero observations).

The 2nd data-step should work fine.
I can submit it over here.

Very weird!

Koen