SAS Di Transformations

[Patrick]

Oh! DIS4.9 metadata is not backward compatible with DIS4.4 and though you won't be able to import the sample job. That's a shame!

I believe DIS4.4 also doesn't have the conditional start/end transformations. That will make it quite a bit harder to share anything on DIS level. Not sure right now how to do it.

Let me start with asking a question:

- Do steps 5 and 6 make sense to you? Can you implement this way in your real job?

[Patrick]

Let's simplify this. Not as elaborate but will probably allow me to give you the direction you need.

So here how the flow could look like in DIS4.4

1. Nodes 1 & 2 just create sample data. In your real job the green target tables from these nodes are your source data.

2. Nodes 3 & 4 are out of the box File writer transformations. I trust that you'll be able to use them to create the .csv files.

3. Node 5 checks in the source tables (here the green tables from node 1 & 2) if there is any table with zero rows. This node creates macro variable &attachCSV populated with value 0 if there is any source table with no rows, else populated with value 1

4. Node 6 sends the email. If &attachCSV is 1 then it also attaches the external files connected to the node.

For code 5 & 6: Make sure that in the code tab Code generation mode is set to "User written body" (the default setting).

You can use the following code in node 5 (no change required)

/* checks if all source tables have rows
	 - if all tables with rows: &attachCSV=1
   - if one or more tables with no rows &attachCSV=0
*/
%macro RowCheck();
  %global createCSV;
  %let createCSV=0;
  data _null_;
    if 0 then 
      do;
        %do i=1 %to &_INPUT_count;
          set &&_input&i nobs=_nobs&i;
        %end;
      end;
    call symputx('attachCSV', min(of _nobs:)>0, 'g');
    stop;
  run;
%mend;
%RowCheck();
%put &=attachCSV;

You can use the following code in Node 6. In the last line of below code change the email address to something valid in your environment (for testing likely your own email address).

/* send email
	 - attach CSV if macro variable &attachCSV=1
*/
%macro _sendmail(to,attachCSV=&attachCSV);

  data _null_;
    length recipients $1000;
    recipients=cat("'",tranwrd(compbl(symget("to"))," ","' '"),"'");
    call symputx('to',recipients);
  run;

  filename outbox email "dummy";

    /* no attachements */
  %if &attachCSV=0 %then
    %do;
      data _null_;
        file outbox
          to=(&to)
          subject="Data from process - no attachments"
          ;
        put 'Folks,';
        put 'There is no data today';
        put 'This email sent without attachments';
      run;
    %end;

  /* with attachements */
  %else %if &attachCSV=1 %then
    %do;
      %local attachments;
      %let attachments=;
      %do i=1 %to &_INPUT_count;
        %let attachments=&attachments %unquote(%nrstr(%')&&_INPUT&i%nrstr(%'));
      %end;
      data _null_;
        file outbox
          to=(&to)
          subject="Data from process - with attachments"
          attach=(&attachments);
          ;
        put 'Folks,';
        put 'Attached the latest cut of data';
      run;
    %end;

  filename outbox clear;

%mend;

%_sendmail(recipient@sample.com);

[JJP1]

"%sysfunc(pathname(work))/class.csv

Thanks Patrick.but in .csv file. The above path is not getting recognized by Sas di
It is giving message like there is no path.
If I manually select the work directory then it is working.
Is this issue with SAs Do 4.4 ?kindly help. Is it working on 4.9 please

[Patrick]

Please share the relevant section of your SAS log.

If you're implementing as per the flow I've posted then you're talking about the path defined in the external file object. Make sure that you select for the path to be in double quotes so that the macro function can execute.

[JJP1]

iam following as you suggested,but iam getting error.kindly help

[Patrick]

You're missing the %. It needs to be: %sysfunc(pathname(work)/test.csv

[JJP1]

Thanks patrick.Now the error gone but iam getting error as the file does not exist.please help

[Patrick]

It's probably better for you if you don't use %sysfunc(pathname(work)) but use the browse button and select an existing folder in your environment. Make sure that it's a location where you've got write access.

You also need really to start and look into the SAS log. Using %sysfunc(pathname(work)) creates the files in the temporary SAS Work location and that's why you get the error when trying to open them via DIS. This doesn't mean the files haven't been created - the DIS wizard just can't show them properly. That goes away if you choose a permanent folder location (any path on the file system where you're allowed to create objects). If you want to use Work in the end then you can always change the path at the end before your last unit test and packaging a release.

IF you would check the SAS log then you can check directly if a file has been created or not. Some log entry like below will tell you...

NOTE: The file "/opt/work/SAS_workFEBF000064BE_xxx/SAS_workA37B000064BE_xxx/class2.csv" 
      is: .....

[JJP1]

Thanks patrick.I came to final step where it will trigger email.
email is not getting triggered.but warning is coming.
really sorry to trouble you

same email code i copied and pasted just only given my actual mail id in beloe mentioned double quotes.

%_sendmail("");

[Patrick]

And why did you add the double quotes??

[JJP1]

i did not added double quotes,sorry mistakenly i mentioned.but iam following as you instructed.
but i am getting the mentioned warning and not getting any e-mail please

[Patrick]

I see. Collision with macro names. Fixed and tested in below code bits. You should be able to just use them and copy over what you've got currently.

Here the amended code for "uw - check source". 

/* checks if all source tables have rows
	 - if all tables with rows: &attachFLG=1
   - if one or more tables with no rows &attachFLG=0
*/
%macro RowCheck();
  %global attachFLG;
  %let attachFLG=0;
  data _null_;
    if 0 then 
      do;
        %do i=1 %to &_INPUT_count;
          set &&_input&i nobs=_nobs&i;
        %end;
      end;
    call symputx('attachFLG', min(of _nobs:)>0, 'g');
    stop;
  run;
%mend;
%RowCheck();
%put &=attachFLG;

...and here for "uw - send mail". Just change the email address before testing.

/* send email
	 - attach CSV if macro variable &attachFLG=1
*/
%macro _sendmail(to,attachCSV=&attachFLG);

  data _null_;
    length recipients $1000;
    recipients=cat("'",tranwrd(compbl(symget("to"))," ","' '"),"'");
    call symputx('to',recipients);
  run;

  filename outbox email "dummy";

    /* no attachements */
  %if &attachCSV=0 %then
    %do;
      data _null_;
        file outbox
          to=(&to)
          subject="Data from process - no attachments"
          ;
        put 'Folks,';
        put 'There is no data today';
        put 'This email sent without attachments';
      run;
    %end;

  /* with attachements */
  %else %if &attachCSV=1 %then
    %do;
      %local attachments;
      %let attachments=;
      %do i=1 %to &_INPUT_count;
        %let attachments=&attachments %unquote(%nrstr(%')&&_INPUT&i%nrstr(%'));
      %end;
      data _null_;
        file outbox
          to=(&to)
          subject="Data from process - with attachments"
          attach=(&attachments);
          ;
        put 'Folks,';
        put 'Attached the latest cut of data';
      run;
    %end;

  filename outbox clear;

%mend;
%_sendmail(recipient1@sample.com recipient2@sample.com );

[JJP1]

Thanks you so much @Patrick  and it is working as expected.
i need to add below fields also while sending email(to and subject already taken care is user written code).
can i include in the same e-mail userwritten code please
from ="xxxx.com"
sender="xxxx.com"
to="tttt.com"
cc="uuuu.com"
subject=
importance='high'

[Patrick]

I've shared already the %_sendmail() macro code with you which demonstrates how to pass a TO as macro parameter and how that's then used in the SAS data step which sends the email. 

You can do similar things for other email directives like CC, Subject or whatever. What's available is documented here.

Please give it first a go yourself and try to amend the macro as you need it.

Let me know how you go. Should you really get stuck then post the code you could come-up with so far and explain where you need help. Should you manage on your own then please mark one of my answers as solution so I know that this question has been resolved.

To develop user written code:

What I'm normally doing is to copy/paste all the DIS code into EG, then have everything upstream to the bit I want to change in one program and the bit I want to change in another one. Then I run everything upstream so I've got all the source tables etc. created for my session and then I'm working on the bit I need to change. Once done I just copy/past the new bit back into the DIS user written transformation and run the DIS job as a last test.  

And to add to above:

The way I'd be changing the macro: Just add the new email directives first hard coded to the data step as in the current code done for subject. Only when that works start to replace the one you need dynamic to replace with macro parameters. Developing this way will make debugging easier in case something doesn't work. Doing it in EG (or SAS Studio) makes changing and re-running the code easier than using DIS (at least in my opinion).

[JJP1]

Iam able to get email as per my expectations.
sorry to trouble again.but i am trying to add from option,i added as shown below,job is running.but from option is not getting detected and iam not getting any e-mail.