BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mfeller
Fluorite | Level 6

I have a data integrity query that if everything is running smoothly, when the query is done, ideally there should be no results.

I want to have a conditional step after that query that send out one of two emails.  

 

1.  If the previous query is null; send out an email saying, great job, no errors.

2.  If the previous query is not null; sent out an email saying; please see corrections and attach the file.

 

Is there an easy way to do this?

 

Let me state, I *only* use the visual interface for Enterprise Guide.  I have zero knowledge of the SAS language or how to integrate SAS programming into my visual EG use.  The answer to my question might be there is no easy way.

 

 

The only way I've been able to do it so far is very convoluted.  I take my query and append a dummy result, sort it and in the conditional step say if the first results equals my appended data, then then the null email.  Else, I have to filter out my appended result so as to not send dummy data, then send out the attached result email.  

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

Don't be too hard on Kurt. When you've used SAS for a long time, you become a very fervent advocate!

 

However, it is possible to meet your needs using only EG. Here's how:

 

1. I'm assuming you know how to set up your steps to send out the emails.

2. Use the query builder on the result dataset of your query; drag any column into the "select data" grid, and set the Summary option to "Count".

3. When you run the query, you'll get a single observation with a single variable which will be the count of non-null values in the column. If everything went well, I assume this will be zero, otherwise it will be non-zero.

4. You can now hang your dependent email tasks off of this query result, and set a condition to execute them based on whether the value of the contents is zero or not.

 

Tom

View solution in original post

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, it depends, your on EG so its a bit different to my SAS.  What is created by the previous step?  If there are no errors, does it create a dataset with zero observations, or no dataset at all?  If it is zero observations then something like:

data _null_;
  set sashelp.vtable (where=(libname="<your_libname" and memname="<your_dataset>" and nobs ne 0));
  call execute('filename mymail email ....;');
run;

I.e. the call execute will only run if there are any observations with the where clause, i.e. if you have that dataset and it has observations.

 

If it doesn't create a dataset at all then you may want to look at exists function:

data _null_;
  if exists(<your_libname>.<your_dataset>) then call execute('filename mymail email ....;');
run;
Kurt_Bremser
Super User

If you know the name of the dataset in question, you can do something like:

%macro check_if_null(dataset);

data _null_;
if done and _n_ = 1 then call symput('errorcheck','no');
if _n_ = 2 then do; call symput('errorcheck','yes'); stop; end;
set &dataset end=done;
run;

%put errorcheck=&errorcheck;

%mend;

Instead of the %put, you can insert your code for sending emails.

mfeller
Fluorite | Level 6

I edited my post to state that I'm trying to do this solely in the EG visual tool, as I don't know any SAS programming.

 

Thanks!

mfeller
Fluorite | Level 6

Wow, thank you for that completely unhelpful reply.  

TomKari
Onyx | Level 15

Don't be too hard on Kurt. When you've used SAS for a long time, you become a very fervent advocate!

 

However, it is possible to meet your needs using only EG. Here's how:

 

1. I'm assuming you know how to set up your steps to send out the emails.

2. Use the query builder on the result dataset of your query; drag any column into the "select data" grid, and set the Summary option to "Count".

3. When you run the query, you'll get a single observation with a single variable which will be the count of non-null values in the column. If everything went well, I assume this will be zero, otherwise it will be non-zero.

4. You can now hang your dependent email tasks off of this query result, and set a condition to execute them based on whether the value of the contents is zero or not.

 

Tom

mfeller
Fluorite | Level 6

This response is perfect.  I'm going to clean up my queries that have conditional logic now.  Thanks!

 

P.S.  As for Kurt, this was my first post ever to this forum.  Telling me to learn SAS programming is not helpful at all to my current predicament.  

Reeza
Super User

When you ask for help, you get what you get. Someday you may want his help so no point being rude either.

mfeller
Fluorite | Level 6

I asked for an apple, and was told I don't want to eat an apple, I should want a pomegranate.

I think responses like that should be discouraged as it's not helpful for anyone in the community.  

 

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
  • 9 replies
  • 3533 views
  • 4 likes
  • 5 in conversation