topic Re: Send Email if Nobs - if not, dont. in SAS Programming

Send Email if Nobs - if not, dont.

Kelroy22 — Mon, 25 Jun 2018 14:16:44 GMT

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;

Re: Send Email if Nobs - if not, dont.

Kurt_Bremser — Mon, 25 Jun 2018 14:27:08 GMT

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;

Re: Send Email if Nobs - if not, dont.

RW9 — Mon, 25 Jun 2018 14:29:30 GMT

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;

Re: Send Email if Nobs - if not, dont.

Astounding — Mon, 25 Jun 2018 14:45:38 GMT

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.

Re: Send Email if Nobs - if not, dont.

RW9 — Mon, 25 Jun 2018 15:15:32 GMT

Nice, I had never thought of using the set that way.

Re: Send Email if Nobs - if not, dont.

Kelroy22 — Tue, 26 Jun 2018 10:57:16 GMT

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;