Desktop productivity for business analysts and programmers

export data set to excel only when data set contains data

Reply
Contributor ak2
Contributor
Posts: 27

export data set to excel only when data set contains data

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?

 

 

Super User
Super User
Posts: 7,720

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

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.

 

SAS Super FREQ
Posts: 306

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

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;

 

SAS Super FREQ
Posts: 306

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

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.

Contributor ak2
Contributor
Posts: 27

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

Both methods worked just fine. Thanks a lot!

Ask a Question
Discussion stats
  • 4 replies
  • 417 views
  • 0 likes
  • 3 in conversation