topic Re: export data set to excel only when data set contains data in SAS Enterprise Guide

export data set to excel only when data set contains data

ak2 — Thu, 10 Dec 2015 13:51:44 GMT

Hi.

How can I export my data set to excel if and only if my data set contains values? I run my code on a daily basis, and if a condition is met, then I want to export my table to excel. If the condition is not met, I don't want to export my table. i.e.:

I ran my code yesterday, and col1 have 'a' in all rows. I don't want to export my table to excel today.

id	col1
1	a
2	a
3	a
4	a

Then I run my code today, and col1 is missing 'a' in 2 rows. I wan't to export my table to excel today.

id	col1
1
2	a
3
4	a

How can I wirte a code that only exports my table to excel if col1 has missing values?

Re: export data set to excel only when data set contains data

RW9 — Thu, 10 Dec 2015 14:51:39 GMT

Well, several ways of doing this. Tomy mind the simplest is:

data _null_;
  set have (obs=1 where=(col1=""));
  call execute('proc export data=have outfile="c:\temp.xls"; run;');
run;

So what this does is take one observation (obs=1) from HAVE dataset where a missing is found. If a record exists for that logic then the text in the call execute is sent to the compiler after the datastep is finished. If no records are returned, i.e. all are present, then that call execute code is never carried out.

Re: export data set to excel only when data set contains data

CaseySmith — Thu, 10 Dec 2015 15:00:19 GMT

Here is what I came up with, but I like @RW9's solution *much* better for brevity and efficiency, assuming you don't need the actual count of missing values:

/* count the number of missing values and store result in a macro variable */
proc sql noprint;
	select count(case when MISSING(col1) then "count me" end)
	into :missingCount
	from mydata;
quit;

/* if one or more missing values found, export */
%macro exportIfMissing;
%if &missingCount > 0 %then %do;
	%put NOTE: Missing values found, so exporting to Excel...;
	proc export data=mydata outfile="c:\temp\mydata.xlsx";
	run;
%end;
%else %do;
	%put NOTE: No missing values found.  So, not exporting;
%end;
%mend;
%exportIfMissing;

Re: export data set to excel only when data set contains data

CaseySmith — Thu, 10 Dec 2015 15:40:11 GMT

As an aside...

You can create conditions in EG process flows to control the order of execution with if/then/else logic. These EG process flow conditions can be based on macro variable values. So, storing the "missing value present" result in a macro variable would allow you to use it in a condition in an EG process flow. For example, if you wanted to use the point-and-click Export as a Step in Project task to export the data rather than code (some do, some don't), you could use the macro variable value (which would reflect whether missing values were present) to decide whether to run the task during a process flow or project run.

Note: EG process flow conditions are only applicable when executed in the context of EG. So, if you plan to execute outside of EG, you should stick with code to do the conditional logic. For programmers, the code is often easier anyway.

Re: export data set to excel only when data set contains data

ak2 — Fri, 11 Dec 2015 10:39:07 GMT

Both methods worked just fine. Thanks a lot!