topic Re: How to stop part of query if dataset is empty in SAS Programming

How to stop part of query if dataset is empty

wixol87 — Wed, 17 Nov 2021 07:07:46 GMT

Hi all!

I have some SAS-jobs that runs each evening that creates datasets that is used to update dashboards. These tables are appended to already existing datasets.

Sometimes something in these SAS-jobs goes wrong so the datasets is empty, often because there are some capacity issues in the central systems that makes some queries run empty. And if these empty datasets then are appended, then the dashboards shows nothing.

I would rather have a day old data in the dashboards then no data at all and hope the sas-jobs runs fine the next day. I can't find good examples that helps me with what I'm trying to achieve.

So how can I stop the appending of empty datasets, but also let the code below this appending continue to run?

Something like:

if obs > 0 in new_dataset then do;

proc sql; delete from Server.old_dataset; quit;

proc append base=Server.old_dataset data=new_dataset;
run;

end;

Re: How to stop part of query if dataset is empty

Kurt_Bremser — Wed, 17 Nov 2021 07:20:23 GMT

Retrieve the obs number from DICTIONARY.TABLES, and use it in a macro condition:

%let nobs = 0; /* in case dataset does not exist at all */
proc sql noprint;
select nobs into :nobs from dictionary.tables
where libname = "WORK" and mename = "NEW_DATASET"; /* upper case here! */
quit;

%if &nobs. gt 0
%then %do;
/* remaining code */
%end;

Re: How to stop part of query if dataset is empty

wixol87 — Wed, 17 Nov 2021 07:51:03 GMT

Hi.
Thanks for answering.

I see that the first part of your code runs smoothly and I get a output in the macro. I however get the error-message below on the %IF-statement, and I see that the code I have in the "remaining code" runs whether or not the macro is 0 or greater than 0:

ERROR: The %IF statement is not valid in open code.
27
28 %if &nobs. gt 0
29 %then %do;

Re: How to stop part of query if dataset is empty

Kurt_Bremser — Wed, 17 Nov 2021 08:15:47 GMT

Have your SAS admins update to the current maintenance level 3 days before yesterday. There are so many goodies in there (including the use of %IF %THEN %DO %END in "open code") that not updating (which only takes some work, updates are included in your license) is simply dumb.

Having said that, you need to wrap it into a macro for the time being:

%macro rest_of_code;
%if &nobs. gt 0
%then %do;
/* remaining code */
%end;
%mend;
%rest_of_code

Re: How to stop part of query if dataset is empty

sbxkoenk — Wed, 17 Nov 2021 09:02:16 GMT

Hello @wixol87 ,

This is not responding "1-on-1" on your request, but maybe you can use "exit" functionality in your macro.

I like it and use it from time-to-time.

Example: Providing Exits in a Large Macro

The %GOTO statement is useful in large macros when you want to provide an exit if an error occurs.

%macro check(parm);
   %local status;
   %if &parm= %then %do;
       %put ERROR:  You must supply a parameter to macro CHECK.;
       %goto exit;
   %end;
   more macro statements that test for error conditions 
   %if &status > 0 %then %do;
       %put ERROR:  File is empty.;
       %goto exit;
   %end;
   more macro statements that generate text 
   %put Check completed successfully.;
%exit: %mend check;

Koen

Re: How to stop part of query if dataset is empty

wixol87 — Wed, 17 Nov 2021 09:35:09 GMT

That worked like a charm!

Thanks for helping me. Have a great day!

Re: How to stop part of query if dataset is empty

wixol87 — Wed, 17 Nov 2021 09:36:08 GMT

Thanks for this. I'll have it in mind if a come across a problem where this could be useful 🙂