Controlling export (ODS or PROC) based on Number of Observations

Question

I currently have a SAS process that generates multiple data sets (whether they have observations or not). I want to determine a way to control the export procedure based on the total number of observations (if nobs > 0, then export). My first attempt was something primitive using if/then logic comparing a select into macro var (counting obs in a data set) -

DATA _NULL_;
   SET A_EXISTS_ON_B;
   IF &A_E > 0 THEN DO;
      FILE "C:\Users\ME\Desktop\WORKLIST_T &PDAY..xls";
      PUT TASK;
   END;
RUN;

The issue here is that I don't have a way to write multiple sets to the same workbook with multiple sheets(or do I?)

In addition, whenever I try and add another "Do" block, with similar logic, the execution fails. If this cannot be done with a data null, would ODS be the answer?

Answer 1

The core of what you want to do, conditionally execute code, can be done one of a number of ways.

Let's imagine we have a short macro that exports a dataset to excel. Simple as pie.

%macro export_to_excel(data=,file=,sheet=);
 proc export data=&data. outfile=&file. dbms=excel replace;
  sheet=&sheet.;
 run;
%mend export_to_excel;

Now let's say we want to do this conditionally. How we do it depends, to some degree, on how we call this macro in our code now.

Let's say you have:

%let wherecondition=1; *always true!;

data class;
  set sashelp.class;
  if &wherecondition. then output;
run;

%export_to_excel(data=class,file="c:\temp\class.xlsx", sheet=class1);

Now you want to make this so it only exports if class has some rows in it, right. So you get the # of obs in class:

proc sql;
  select count(1) into :classobs from class;
quit;

And now you need to incorporate that somehow. In this case, the easiest way is to add a condition to the export macro. Open code doesn't allow conditional executing of code, so it needs to be in a macro.

So we do:

%macro export_to_excel(data=,file=,sheet=,condition=1);
 %if &condition. %then %do;
  proc export data=&data. outfile=&file. dbms=excel replace;
   sheet=&sheet.;
  run;
 %end;
%mend export_to_excel;

And you add the count to the call:

%export_to_excel(data=class,file="c:\temp\class.xlsx", sheet=class1,condition=&classobs.)

Tada, now it won't try to export when it's 0. Great.

---

If this code is already in a macro, you don't have to alter the export macro itself. You can simply put that %if %then part around the macro call. But that's only if the whole thing is already a macro - %if isn't allowed outside of macros (sorry).

---

Now, if you're exporting a whole bunch of datasets, and you're generating your export calls from something, you can add the condition there, more easily and more smoothly than this.

Basically, either make by hand (if that makes sense), or use proc sql or proc contents or (other method of your choice) to make a dataset that contains one row per dataset-to-export, with four variables: dataset name, file to export, sheet to export (unless that's the same as the dataset name), and count of observations for that dataset. Often the first three would be made by hand, and then merged/updated via sql or something else to the count of obs per dataset.

Then you can generate calls to export, like so:

proc sql;
 select cats('%export_to_excel(data=',dataname,',file=',filename,',sheet=',sheetname,')')
  into :explist separated by ' '
  from datasetwithnames
  where obsnum>0;
quit;

&explist.;  *this actually executes them;

Assuming obsnum is the new variable you created with the # of obs, and the other variables are obviously named. That won't pull a line with anything with 0 observations - so it never tries to execute the export. That works with the initial export macro just as well as with the modified one.

Answer 2

Suggest you google around for different approaches to writing XLS files.

Regarding using a DATA step or PROC step, the DATA step is tolerant of datasets that have 0 obs. If the SET statement reads a dataset that has 0 obs, it will simply end the step. So you don't need special logic. Most PROCS also accomodate 0 obs dataset without throwing a warning or error.

For example:

1218  *Make a 0 obs dataset;
1219  data empty;
1220    x=1;
1221    stop;
1222  run;

NOTE: The data set WORK.EMPTY has 0 observations and 1 variables.

1223
1224  data want;
1225    put "I run before SET statement.";
1226    set empty;
1227    put "I do not run after SET statement.";
1228  run;

I run before SET statement.
NOTE: There were 0 observations read from the data set WORK.EMPTY.
NOTE: The data set WORK.WANT has 0 observations and 1 variables.

1229
1230  proc print data=empty;
1231  run;

NOTE: No observations in data set WORK.EMPTY.

But note as Joe points out, PROC EXPORT will happily export a dataset with 0 obs and write an file with 0 records, overwriting if it was there already. e.g.:

1582  proc export data=sashelp.class outfile="d:\junk\class.xls";
1583  run;

NOTE: File "d:\junk\class.xls" will be created if the export process succeeds.
NOTE: "CLASS" range/sheet was successfully created.

1584
1585  data class;
1586    stop;
1587    set sashelp.class;
1588  run;

NOTE: The data set WORK.CLASS has 0 observations and 5 variables.

1589
1590  *This will replace class.xls";
1591  proc export data=class outfile="d:\junk\class.xls" replace;
1592  run;

NOTE: "CLASS" range/sheet was successfully created.

ODS statements would likely do the same.

I use a macro to check if a dataset is empty. SO answers like: How to detect how many observations in a dataset (or if it is empty), in SAS?