Hi. Ive made a macro that should send an email of there are observations in the dataset control.
Unfortunately it doens't wok. Can some of you guys see what im doing wrong?
%macro doit;
%let id=%sysfunc(open(Control));
%let NObs=%sysfunc(attrn(&id,NOBS));
%syscall set(id);
%do i=1 %to &NObs;
%let rc=%sysfunc(fetchobs(&id,&i));
filename mymail email
subject="Risk Control IRK: Violation in HQLA Concentration Risk"
to="SAN@RISK.DK"
data _null_;
file mymail;
put "HQLA";
put "- Paper : &papirnavn";
put "- Nominal : &NominalBase";
put "----------------------"; run;
run;
%
%end;
%let id=sysfunc(close(&id));
%mend;
%doit;
Retrieve the number from dictionary.tables:
%macro doit;
proc sql noprint;
select nobs into :nobs
from dictionary.tables
where libname = 'WORK' and memname = 'CONTROL';
quit;
%if &nobs ne 0
%then %do;
filename mymail email
subject="Risk Control IRK: Violation in HQLA Concentration Risk"
to="SAN@RISK.DK"
;
data _null_;
file mymail;
put "HQLA";
put "- Paper : &papirnavn";
put "- Nominal : &NominalBase";
put "----------------------";
run;
%end;
%mend;
%doit;
Retrieve the number from dictionary.tables:
%macro doit;
proc sql noprint;
select nobs into :nobs
from dictionary.tables
where libname = 'WORK' and memname = 'CONTROL';
quit;
%if &nobs ne 0
%then %do;
filename mymail email
subject="Risk Control IRK: Violation in HQLA Concentration Risk"
to="SAN@RISK.DK"
;
data _null_;
file mymail;
put "HQLA";
put "- Paper : &papirnavn";
put "- Nominal : &NominalBase";
put "----------------------";
run;
%end;
%mend;
%doit;
My solution ended like this. Thank you for your help. /Steffan
%macro doit;
proc sql noprint;
select nobs into :nobs
from dictionary.tables
where libname = 'WORK' and memname = 'CONTROL';
quit;
%if &nobs ne 0 %then
%do;
filename mymail email
subject="Risk Control IRK: Violation in HQLA Concentration Risk"
to="san@al-bank.dk"
attach= (&HQLA. content_type="application/xlsx")
CONTENT_TYPE="text/html";
ods listing close;
ods html body=mymail;
proc print data=work.control;
run;
ods html close;
ods listing;
%end;
%mend;
%doit;
Simpler:
%macro doit();
filename mymail email...;
data _null_;
...;
run;
%mend doit;
data _null_; set sashelp.vtable (where=(memname="CONTROL")); if nobs=0 then call execute('%doit;'); run;
It looks like you are complicating the problem. Macro language is not needed (except perhaps for the macro variables &PAPIRNAVN and &NOMINALBASE). Consider this DATA step:
data _null_;
set control;
file mymail;
put .......................................;
stop;
run;
When there are zero observations, the SET statement halts the DATA step. When there are observations, the rest of the DATA step proceeds once, then stops.
Nice, I had never thought of using the set that way.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.