sas insert table into html email

Question

Is it possible to loop through the records of a table to populate an html email without repeating the beginning and the end of the email?

With this example I get a mail with 5 tables of 1 row (because WORK.MyEmailTable is table of 5 records and set creates a loop in the data step):

data _null_;
   file mymail;
   set WORK.MyEmailTable;

   put '<html><body><table>';

   ***loop through all records;
   put '<tr>';
   put %sysfunc(cats('<td>',var1,'</td>'));
   put %sysfunc(cats('<td>',var2,'</td>'));
   put %sysfunc(cats('<td>',var3,'</td>'));
   put '</tr>';

   put '</table></body></html>';

run;

And I'm looking to have 1 table of 5 rows.

I don't know if there is a way to prevent recursively put the beginning and the end of the mail when you use set in the data step.

(Let me know if it's not clear I'll update.)

Thank you,

score 2 · Accepted Answer · answered Oct 21 '15 at 14:40

You can use the _n_ automatic datastep variable to let you know when you are on the first observation, and the set statement option end= to know that you are on the last observation:

data _null_;
    file mymail;
    set WORK.MyEmailTable end=eof;

    if _n_ eq 1 then do;
      put '<html><body><table>';
    end;

    /*loop trhough all records*/
    put '<tr>';
    put %sysfunc(cats('<td>','_n_=',n,' eof=',eof,' ',var1,'</td>'));
    put %sysfunc(cats('<td>','_n_=',n,' eof=',eof,' ',var2,'</td>'));
    put %sysfunc(cats('<td>','_n_=',n,' eof=',eof,' ',var3,'</td>'));
    put '</tr>';

    if eof then do;
      put '</table></body></html>';
    end;
run;

I've added the values _n_ and eof to the output so you can see clearly how they work.

score 1 · Answer 2 · answered Oct 21 '15 at 15:02

Rob's method is pretty much the standard, but there is another option if you prefer scripting an explicit loop (which can be more comfortable for non-SAS programmers to read). This will function exactly like Rob's answer, and may well compile to the same machine code even.

data _null_;
    file mymail;

    put '<html><body><table>';
    do _n_ = 1 by 1 until (eof);
    /*loop trhough all records*/
      set WORK.MyEmailTable end=eof;
      put '<tr>';
      put %sysfunc(cats('<td>',var1,'</td>'));
      put %sysfunc(cats('<td>',var2,'</td>'));
      put %sysfunc(cats('<td>',var3,'</td>'));
      put '</tr>';
    end;
    put '</table></body></html>';
    stop;
run;

_n_ here doesn't have any special meaning (like it does in Rob's answer); it's used by convention since this way it does effectively have the same meaning as it does normally.

You need to use the end=eof to create a variable eof which is true on the last record of the dataset; otherwise the data step will terminate prematurely (before actually hitting your final statement). You also need the stop to tell it to not go back to the start - otherwise it will, and will put a new starting section, then terminate instantly when it hits the set. (Try it and see.)

do _n_=1 by 1 until (eof); is a SAS-specific way of using an incremental loop; it's similar to the c/c++ for (_n_=1; !eof; _n_++) for example - it allows you to have an auto-incremented do loop whilst having a separate, unrelated stopping criteria.

sas insert table into html email

2 Answers2