BookmarkSubscribeRSS Feed
ak2
Calcite | Level 5 ak2
Calcite | Level 5

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?

 

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

CaseySmith
SAS Employee

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;

 


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

CaseySmith
SAS Employee

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.


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

ak2
Calcite | Level 5 ak2
Calcite | Level 5

Both methods worked just fine. Thanks a lot!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2888 views
  • 1 like
  • 3 in conversation