Hi all
Below is my code.
I would like to do :
If work.CUSTOM1 is available/exist then send out an email to notify then STOP, with no further action. if data not available then process the data step as per usual.
The code below it allows to send an email when CUSTOM1 is available, however the process step runs as well. I want it to stop processing when we know CUSTOM1 is there. Thanks in advance.
data _null_;
if exist("work.CUSTOM1") then
call execute(catt(
"filename send email "
," to=('test@abc.com')"
," type='text/html' "
," subject=""Data_FAILED"" "
,"from='test@abc.com' ;"
,"ods html body=send;"
,"ods html close;"
,"filename send clear;" ));
else;
data original_data;
input var1 $ CON_DATE_START :ddmmyy10. PL_DATE_START :ddmmyy10. SS_DATE_START :ddmmyy10.;
format CON_DATE_START ddmmyy10. PL_DATE_START ddmmyy10. SS_DATE_START ddmmyy10.;
datalines;
A 12/12/2008 11/01/2022 05/10/2007
B 19/01/2001 11/02/2008 01/03/2020
C 23/10/2008 02/08/2019 02/02/2010
D 01/09/2002 01/01/2000 19/03/2006
;
run;
run;
This is where using the macro language is better:
%if %sysfunc(exist(work.CUSTOM1))
%then %do;
filename send email
to=('test@abc.com')
type='text/html'
subject="Data_FAILED"
from='test@abc.com'
;
ods html body=send;
ods html close;
filename send clear;
%end;
%else %do;
data original_data;
input var1 $ CON_DATE_START :ddmmyy10. PL_DATE_START :ddmmyy10. SS_DATE_START :ddmmyy10.;
format CON_DATE_START ddmmyy10. PL_DATE_START ddmmyy10. SS_DATE_START ddmmyy10.;
datalines;
A 12/12/2008 11/01/2022 05/10/2007
B 19/01/2001 11/02/2008 01/03/2020
C 23/10/2008 02/08/2019 02/02/2010
D 01/09/2002 01/01/2000 19/03/2006
;
%end;
Untested, posted from my tablet.
It doesnt seem to be working.
With above code you advised:
When work.CUSTOM1 is available, it doesnt send out email, and no further action.
When work.CUSTOM1 is NOT available, it doesnt process the data step.
I suspected I might missed something in the code. Thanks
"Isn't working" is awful vague.
Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.
No output? Post any log in a code box.
Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.
Add diagnostics:
%if %sysfunc(exist(work.CUSTOM1))
%then %do;
%put Dataset found!;
filename send email
to=('test@abc.com')
type='text/html'
subject="Data_FAILED"
from='test@abc.com'
;
ods html body=send;
ods html close;
filename send clear;
%end;
%else %do;
%put Dataset not found!;
data ;
input var1 $ CON_DATE_START :ddmmyy10. PL_DATE_START :ddmmyy10. SS_DATE_START :ddmmyy10.;
format CON_DATE_START ddmmyy10. PL_DATE_START ddmmyy10. SS_DATE_START ddmmyy10.;
datalines;
A 12/12/2008 11/01/2022 05/10/2007
B 19/01/2001 11/02/2008 01/03/2020
C 23/10/2008 02/08/2019 02/02/2010
D 01/09/2002 01/01/2000 19/03/2006
;
%end;
Run the code in both situations, and post the complete log, using this button:
1 The SAS System 15:04 Thursday, October 13, 2022 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROJECTPATH=''; 5 %LET _CLIENTPROJECTNAME=''; 6 %LET _SASPROGRAMFILE=; 7 8 ODS _ALL_ CLOSE; 9 OPTIONS DEV=ACTIVEX; 10 GOPTIONS XPIXELS=0 YPIXELS=0; 11 FILENAME EGSR TEMP; 12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 13 STYLE=HtmlBlue 14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 15 NOGTITLE 16 NOGFOOTNOTE 17 GPATH=&sasworklocation 18 ENCODING=UTF8 19 options(rolap="on") 20 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 21 22 GOPTIONS ACCESSIBLE; 23 %macro test; 24 %if %sysfunc(exist(work.CUSTOM1)) 25 %then %do; 26 %put Dataset found!; 27 filename send email 28 to=('test@abc.com') 29 type='text/html' 30 subject="Data_FAILED" 31 from='test@abc.com' 32 ; 33 ods html body=send; 34 ods html close; 35 filename send clear; 36 %end; 37 %else %do; 38 %put Dataset not found!; 39 data ; 40 input var1 $ CON_DATE_START :ddmmyy10. PL_DATE_START :ddmmyy10. SS_DATE_START :ddmmyy10.; 41 format CON_DATE_START ddmmyy10. PL_DATE_START ddmmyy10. SS_DATE_START ddmmyy10.; 42 datalines; 43 A 12/12/2008 11/01/2022 05/10/2007 44 B 19/01/2001 11/02/2008 01/03/2020 45 C 23/10/2008 02/08/2019 02/02/2010 46 D 01/09/2002 01/01/2000 19/03/2006 2 The SAS System 15:04 Thursday, October 13, 2022 47 ; 48 %end; 49 %mend; 50 51 GOPTIONS NOACCESSIBLE; 52 %LET _CLIENTTASKLABEL=; 53 %LET _CLIENTPROJECTPATH=; 54 %LET _CLIENTPROJECTNAME=; 55 %LET _SASPROGRAMFILE=; 56 57 ;*';*";*/;quit;run; 58 ODS _ALL_ CLOSE; 59 60 61 QUIT; RUN; 62
The code runs fine with no error, but no email send when 'work.CUSTOM1' is available
when 'work.CUSTOM1' is unavailable, the data step doesnt generate.
Please see above log. Thanks in advance
I haven't tried to send an email with SAS in decades.
But it looks like you are trying to use the EMAIL fileref engine to trigger the sending of the email.
But you never actually send any content to the fileref you created.
You pointed ODS output at that fileref, but then you closed it without ever sending anything to it.
What was you it you planned to send as the CONTENT of the email?
You define a macro, but you never call it, so nothing happens.
Please note that my code has no macro definition, as %IF can be used in "open code" nowadays. Use my code as posted.
1 The SAS System 09:08 Friday, October 14, 2022 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROJECTPATH=''; 5 %LET _CLIENTPROJECTNAME=''; 6 %LET _SASPROGRAMFILE=; 7 8 ODS _ALL_ CLOSE; 9 OPTIONS DEV=ACTIVEX; 10 GOPTIONS XPIXELS=0 YPIXELS=0; 11 FILENAME EGSR TEMP; 12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 13 STYLE=HtmlBlue 14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 15 NOGTITLE 16 NOGFOOTNOTE 17 GPATH=&sasworklocation 18 ENCODING=UTF8 19 options(rolap="on") 20 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 21 22 GOPTIONS ACCESSIBLE; ERROR: The %IF statement is not valid in open code. 23 %if %sysfunc(exist(work.CUSTOM1)) 24 %then %do; 25 %put Dataset found!; Dataset found! 26 filename send email 27 to=('test@abc.com') 28 type='text/html' 29 subject="Data_FAILED" 30 from='test@abc.com' 31 ; 32 ods html body=send; NOTE: Writing HTML Body file: SEND 33 ods html close; Message sent To: 'test@abc.com' Cc: Bcc: Subject: Data_FAILED Attachments: 34 filename send clear; NOTE: Fileref SEND has been deassigned. ERROR: The %END statement is not valid in open code. ERROR: The %ELSE statement is not valid in open code. 2 The SAS System 09:08 Friday, October 14, 2022 35 36 %end; 37 %else %do; 38 %put Dataset not found!; Dataset not found! 39 data ; 40 input var1 $ CON_DATE_START :ddmmyy10. PL_DATE_START :ddmmyy10. SS_DATE_START :ddmmyy10.; 41 format CON_DATE_START ddmmyy10. PL_DATE_START ddmmyy10. SS_DATE_START ddmmyy10.; 42 datalines; NOTE: The data set WORK.DATA5 has 4 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 739.59k OS Memory 22108.00k Timestamp 14/10/2022 11:15:13 AM Step Count 7 Switch Count 46 47 ; 48 49 %end; ERROR: The %END statement is not valid in open code. 50 51 GOPTIONS NOACCESSIBLE; 52 %LET _CLIENTTASKLABEL=; 53 %LET _CLIENTPROJECTPATH=; 54 %LET _CLIENTPROJECTNAME=; 55 %LET _SASPROGRAMFILE=; 56 57 ;*';*";*/;quit;run; 58 ODS _ALL_ CLOSE; 59 60 61 QUIT; RUN; 62
Sorry for the confusion.
Email generate and datastep run, when 'work.CUSTOM1' is available
Email generate and datastep run, also when 'work.CUSTOM1' is unavailable
I thought 'else' statement will only execute when data 'work.CUSTOM1' is unavailable?
Thanks for your time.
The log you show us only compiles the macro but doesn't execute it. If you would execute you'd get an error because you can't have a DATALINES statement within a macro.
While DATALINES cannot be used in a macro, it can be used in an "open code" %IF.
So do this:
%if %sysfunc(exist(work.CUSTOM1))
%then %do;
%put Dataset found!;
filename send email
to=('test@abc.com')
type='text/html'
subject="Data_FAILED"
from='test@abc.com'
;
ods html body=send;
ods text="something for the mail";
ods html close;
filename send clear;
%end;
%else %do;
%put Dataset not found!;
data dataset;
input var1 $ CON_DATE_START :ddmmyy10. PL_DATE_START :ddmmyy10. SS_DATE_START :ddmmyy10.;
format CON_DATE_START ddmmyy10. PL_DATE_START ddmmyy10. SS_DATE_START ddmmyy10.;
datalines;
A 12/12/2008 11/01/2022 05/10/2007
B 19/01/2001 11/02/2008 01/03/2020
C 23/10/2008 02/08/2019 02/02/2010
D 01/09/2002 01/01/2000 19/03/2006
;
%end;
No macro definition!
The code works!!
But it generates the email when the data does not exist.
1 The SAS System 09:49 Monday, October 17, 2022 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROJECTPATH=''; 5 %LET _CLIENTPROJECTNAME=''; 6 %LET _SASPROGRAMFILE=; 7 8 ODS _ALL_ CLOSE; 9 OPTIONS DEV=ACTIVEX; 10 GOPTIONS XPIXELS=0 YPIXELS=0; 11 FILENAME EGSR TEMP; 12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 13 STYLE=HtmlBlue 14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 15 NOGTITLE 16 NOGFOOTNOTE 17 GPATH=&sasworklocation 18 ENCODING=UTF8 19 options(rolap="on") 20 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 21 22 GOPTIONS ACCESSIBLE; ERROR: The %IF statement is not valid in open code. 23 %if %sysfunc(exist(work.CUSTOM1)) 24 %then %do; 25 %put Dataset found!; Dataset found! 26 filename send email 27 to=('test@abc.com') 28 type='text/html' 29 subject="Data_FAILED" 30 from='test@abc.com' 31 ; 32 ods html body=send; NOTE: Writing HTML Body file: SEND 33 ods text="something for the mail"; 34 ods html close; Message sent To: 'test@abc.com' Cc: Bcc: Subject: Data_FAILED Attachments: 35 filename send clear; NOTE: Fileref SEND has been deassigned. ERROR: The %END statement is not valid in open code. 2 The SAS System 09:49 Monday, October 17, 2022 ERROR: The %ELSE statement is not valid in open code. 36 %end; 37 %else %do; 38 %put Dataset not found!; Dataset not found! 39 data dataset; 40 input var1 $ CON_DATE_START :ddmmyy10. PL_DATE_START :ddmmyy10. SS_DATE_START :ddmmyy10.; 41 format CON_DATE_START ddmmyy10. PL_DATE_START ddmmyy10. SS_DATE_START ddmmyy10.; 42 datalines; NOTE: The data set WORK.DATASET has 4 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 739.53k OS Memory 21852.00k Timestamp 17/10/2022 10:47:45 AM Step Count 6 Switch Count 46 47 ; 48 %end; ERROR: The %END statement is not valid in open code. 49 50 GOPTIONS NOACCESSIBLE; 51 %LET _CLIENTTASKLABEL=; 52 %LET _CLIENTPROJECTPATH=; 53 %LET _CLIENTPROJECTNAME=; 54 %LET _SASPROGRAMFILE=; 55 56 ;*';*";*/;quit;run; 57 ODS _ALL_ CLOSE; 58 59 60 QUIT; RUN; 61
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.