CODEWITHSUNDEEP
sas
vj_petrol
vj_petrol

Reputation: 13

How to skip code if created dataset has zero rows

I have a job which at first imports some xlsx files, then connects to multiple DB tables. Based on conditions, the job selects rows to output, and creates an excel file to send on to the final end-user.

Sometimes, that job returns zero rows, which is acceptable; in that case, I would prefer to create an empty excel file with only the variables, but not run the other code (checking/cleaning code).

How can I conditionally execute code only when there are results?

Something like this:

I get 0 rows

If Result = 0 then Go to *"here"*
Else *"just run the code further"*

Upvotes: 1

Views: 2144

Answers (1)

Joe
Joe

Reputation: 63424

You have a few useful things that can help you here.

First off, PROC SQL sets a macro variable SQLOBS, which is particularly useful in identifying how many records were returned from the last SQL query it ran.

proc sql;
  select * from sashelp.class;
quit;

%put I returned &SQLOBS rows;

You might use this to drive further processing, either with %IF blocks as Tom notes in comments or other methods I will cover below.

You can also check how many rows are in a dataset explicitly, if you prefer a slightly more robust option.

proc sql;
  select count(*) into :class_count from sashelp.class;
quit;

%put I returned &class_count rows;

For very large datasets, there are faster options (using the dataset descriptors, dictionary tables, or a few other options), but for most tables this is fine.

Either way, what I would typically do with a program I intended to run in production would be then to drive the rest of the program from macros.

%macro whatIWantToDo(params);
...
do stuff
...
%mend whatIWantToDo;

proc sql;
  mySqlStuff;
quit;

%if &sqlobs. gt 0 %then %do;
  %whatIWantToDo(params);
%end;
%else %do;
  %put Nothing to do;
%end;

Another option is to use call execute; this is appropriate if your data drives the macro parameters. The big advantage of call execute is that it only runs if you have data rows - if you have zero, it won't do anything!

Say you have some datasets to run code on. You could have up to twelve - one per month - but only have them for the current calendar year, so in Jan you have one, Feb you have two, etc. You could do this:

data mydata_jan mydata_feb mydata_mar;
  set sashelp.class;
run;

%macro printit(data=);
  title "Printing &data.";
  proc print data=&data;
  run;
  title;
%mend printit;
data _null_;
  set sashelp.vtable;
  where upcase(memname) like 'MYDATA_%' and nobs gt 0;
  callstr = cats('%printit(data=',memname,')');
  call execute(Callstr);
run;

First I make the datasets, with a name I can programmatically identify. Then I make the macro that I want to run on each (this could be checking, cleaning, whatever). Then I use sashelp.vtable which shows which tables are created, and check the nobs variable (number of observations) is more than zero. Then I use call execute to run the macro on that dataset!

Upvotes: 2

Related Questions