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.??
@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 🙂
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.
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;
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
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.
@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 🙂
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.