if then else

[miss2223]

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;

[Kurt_Bremser]

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.

[miss2223]

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

[ballardw]

"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.

[Kurt_Bremser]

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:

[miss2223]

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

[Tom]

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?

[Kurt_Bremser]

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.

[miss2223]

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. 

[Patrick]

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.

[Kurt_Bremser]

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!

[miss2223]

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