topic SAS Programing Sending Email Using macro in SAS Programming

SAS Programing Sending Email Using macro

Son_Of_Krypton — Wed, 01 Sep 2021 08:32:49 GMT

I have dataset below :

Below is macro code :

%macro zero_check;
data testing;
set Test3;
if TOTAL_PAYMENTS_TO_DATE_LATEST in (0,.) and TOTAL_PAYMENTS_TO_DATE_PREVIOUS notin (0,.)
then call symputx("val",1);
else call symputx("val",2);
/*run;*/



run;    
%put value=&val;


%if &val=1 %then 
%do;
filename SENDMAIL EMAIL to=("test@email.COM") subject="Found zero on TOTAL PAYMENTS TO DATE of Payments file but had a value previously"; 
data _null_;
	file SENDMAIL;
	put 'This email serves as notification that the value of column TOTAL PAYMENTS TO DATE of Payments file contains zero or blank';
	put /;
	put "File Name: &filename.";
	put /;
	put 'This message was sent from a notification-only email address that does not accept incoming email. Please do not reply to this message.' ;
run;
%end;

%else %do;

%end;
%mend;
%zero_check;

based on above condition the value should be resolved to 2 instead it resolving to 1.

1009      %put value=&val;
value=1

can somebody help me with this.??

Re: SAS Programing Sending Email Using macro

Son_Of_Krypton — Wed, 01 Sep 2021 08:34:26 GMT

anybody can me guide me here how i can change the conditions

Re: SAS Programing Sending Email Using macro

yabwon — Wed, 01 Sep 2021 11:14:01 GMT

1) Could you share some example data for the Test3 dataset?

2) If the Test3 has more than 1 observation then the `if` condition is tested and executed for each observation. So if the last observation resolves condition to false, than value will be 2, even if all previous were resolved to true.

Re: SAS Programing Sending Email Using macro

Quentin — Wed, 01 Sep 2021 12:15:04 GMT

What do you get in the log if you run:

data testing;
  set Test3;
  put (TOTAL_PAYMENTS_TO_DATE_LATEST TOTAL_PAYMENTS_TO_DATE_PREVIOUS)(=);
  if TOTAL_PAYMENTS_TO_DATE_LATEST in (0,.) and TOTAL_PAYMENTS_TO_DATE_PREVIOUS notin (0,.) then call symputx("val",1);
  else call symputx("val",2);
run;    
%put value=&val;

Re: SAS Programing Sending Email Using macro

Son_Of_Krypton — Thu, 02 Sep 2021 08:13:08 GMT

NOTE: 0 observations were read from "WORK.Test3"
NOTE: Data set "WORK.testing" has 0 observation(s) and 3 variable(s)
NOTE: The data step took :
      real time       : 0.001
      user cpu time   : 0.000
      system cpu time : 0.001
      Timestamp       :   02SEP21:10:09:58
      Maximum resident set         : 144152k
      Page Faults                  : 0
      Page Reclaims                : 11
      Page Swaps                   : 0
      Voluntary Context Switches   : 0
      Involuntary Context Switches : 0
      Block Input Operations       : 0
      Block Output Operations      : 8


919       ;
920       %put value=&val.;
value=1
921       quit; run;
922       ODS _ALL_ CLOSE;

I still get value resolved to 1 it should resolve to 2 as dataset is empty

Re: SAS Programing Sending Email Using macro

Son_Of_Krypton — Thu, 02 Sep 2021 08:17:06 GMT

TOTAL_PAYMENTS_TO_DATE_LATEST
0
0
0
0
0
0
0
0
0
0
0
0
0

TOTAL_PAYMENTS_TO_DATE_PREVIOUS
3057.12
647
1073.12
300
968.01
638
745.16
2986.6
1095.89
600
1095.92
1540.23
345

Re: SAS Programing Sending Email Using macro

Kurt_Bremser — Thu, 02 Sep 2021 09:34:59 GMT

NOTE: 0 observations were read from "WORK.Test3"

Since no observation was read, the CALL SYMPUTX was never executed, and the macro variable kept its previous value.

Re: SAS Programing Sending Email Using macro

Quentin — Thu, 02 Sep 2021 13:22:08 GMT

@Son_Of_Krypton wrote:
NOTE: 0 observations were read from "WORK.Test3"
NOTE: Data set "WORK.testing" has 0 observation(s) and 3 variable(s)

920       %put value=&val.;
value=1
921       quit; run;
922       ODS _ALL_ CLOSE;
I still get value resolved to 1 it should resolve to 2 as dataset is empty

As Kurt wrote, your CALL SYMPUTX statement is never executing. If there are 0 obs in work.test3, when the below code runs:

data testing;
  put "You will see me in the log because I execute before the SET statement";
  set Test3;
  put "You will not see me in the log because I never execute";
  put (TOTAL_PAYMENTS_TO_DATE_LATEST TOTAL_PAYMENTS_TO_DATE_PREVIOUS)(=);
  if TOTAL_PAYMENTS_TO_DATE_LATEST in (0,.) and TOTAL_PAYMENTS_TO_DATE_PREVIOUS notin (0,.) then call symputx("val",1);
  else call symputx("val",2);
run;

the step stops executing immediately when the SET statement executes and hits the (logical) end-of-file mark in the dataset. So nothing after the SET statement will execute. If you delete the macro var VAL before this step, you will see that after the step, the macro var VAL will not exist. This is one of the challenges of working with global macro vars: they hang around in your session, and can cause confusion (for the programmer, not SAS 🙂