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!
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
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;
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.
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 wrote:
as I don't know any SAS programming.
Then it's high time you start learning it. All advanced problem solving in SAS requires programming skills.
Wow, thank you for that completely unhelpful reply.
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
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.
When you ask for help, you get what you get. Someday you may want his help so no point being rude either.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.