how do I FTP then Email in SAS

[IMDKY]

I have some code that ftps a file

//FTPSTEP  EXEC CCFTPP00
//INPUT   DD *
123.45.678.9
USERNAME
PASSWORD
CD FLEET/DIRECTORY
PUT 'U00JBK7.FILENAME.TXT' FILENAME.TXT
QUIT;

and some code that sends an email

FILENAME MAIL EMAIL "MYEMAIL@DOMAIN.COM"
                SUBJECT="FILENAME HAS FINISHED";
DATA _NULL_;
    FILE MAIL;
    PUT "FILENAME HAS FINISHED";
RUN;

but for some reason when I have the ftp code in there it's not sending the email. I can't figure out why. Can someone help me with this?

 

[ChrisW75]

That first bit doesn't look like regular SAS code to me, what is that?
Have you tried putting the FTP part after the e-mail part?

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[IMDKY]

I have and it still doesn't work. Someone told me that the ftp part is a macro if that means anything... I'm willing to rewrite if there's another way to ftp and then email.

 

[LarrySteele]

ChrisW75 - to answer your question, the first two lines are JCL.

IMDKY - I don't see your attach argument:

FILENAME MAIL EMAIL 'MYEMAIL@DOMAIN.COM'  
 SUBJECT='FILENAME HAS FINISHED' 
 [b]ATTACH=("FILENAME" EXT='TXT')[/b];

Caveat: I haven't worked with SAS for a few years and only occasionally log on to the mainframe. Still, this is what appears to be a difference between your code and some that we use.

Hope this helps,
Larry

 

[IMDKY]

I've never used an Attach argument. Both the FTP code and the EMAIL code work fine seperately (even without the Attach argument). Do you think it would help?

 

[LarrySteele]

Both the FTP code and the EMAIL code work fine separately (even without the Attach argument).

What I had read from your original post is that you can send an email, but nothing's in the email. Hence, my suggesting you add the Attach argument so the file gets attached.

Now I see that the problem is that no email is sent.

Can you post your complete job? Again, it's been awhile, but might be able to spot what's amiss.

 

[IMDKY]

Here's some example code of a simple job I just ran pulling in a part number. In most cases my output files are too big to attach in an email (which is why I'm looking to FTP). This job FTPs the .TXT file perfectly fine but there's no email. If I remove the FTP code it sends an email.
Names, ip addresses and passwords were replaced.

//ROOTABCS JOB 'TEST BLAH',NAME,MSGLEVEL=(1,1),MSGCLASS=T,
//          NOTIFY=ROOTABC,TIME=25
//*FORMAT  PR,DDNAME=,DEST=LOCAL,FORMS=2605,COPIES=1
//*MAIN  CLASS=F,SYSTEM=ANY
//*
//JAN   EXEC PROC=SAS,SORT=80,OPTIONS='PS=80'
//*
//STEP01    EXEC SAS
//SAS.WORK     DD    SPACE=(CYL,(250,150))
//PSDB      DD DSN=ROOTABC.PSDBSUM.TXT,DISP=(SHR,KEEP)
//OUT       DD DSN=ROOTABC.TOP80OUT.TXT,DISP=OLD
//*

OPTIONS PAGENO=1 LINESIZE=256 PAGESIZE=32767 NOCENTER;

DATA PSDBIN;
INFILE PSDB;
INPUT
     @  9  PARTNUMB $CHAR25.
     ;
     IF PARTNUMB = '123456';

DATA TOP80OUT;
  SET PSDBIN;
  FILE OUT;
  PUT
     @  1  PARTNUMB $CHAR25.
     ;

 /****************************************************************/
//FTPSTEP  EXEC CCFTPP00
//INPUT   DD *
192.168.1.1
USERNAME
PASSWORD
CD PATH\TO\DIRECTORY
PUT 'ROOTABC.TOP80OUT.TXT' TOP80OUT.TXT

FILENAME MAIL EMAIL "TEST@MYEMAIL.COM"
                 SUBJECT="JOB HAS FINISHED";
DATA _NULL_;
    FILE MAIL;
    PUT "JOB HAS FINISHED";
RUN;

 

[LarrySteele]

IMDKY,

I think the problem is that you're trying to have CCFTP00 send your email. If that program is not provisioned to send email, then you need to email from SAS.

HTH,
Larry

 

[IMDKY]

What does that code look like to have sas send the email? I'll give it a try and see if that works...

 

[LarrySteele]

Again, it's been awhile, but something like the following might work:

//ROOTABCS JOB 'TEST BLAH',NAME,MSGLEVEL=(1,1),MSGCLASS=T,
//          NOTIFY=ROOTABC,TIME=25
//*FORMAT  PR,DDNAME=,DEST=LOCAL,FORMS=2605,COPIES=1
//*MAIN  CLASS=F,SYSTEM=ANY
//*
//JAN   EXEC PROC=SAS,SORT=80,OPTIONS='PS=80'
//*
//STEP01    EXEC SAS
//SAS.WORK     DD    SPACE=(CYL,(250,150))
//PSDB      DD DSN=ROOTABC.PSDBSUM.TXT,DISP=(SHR,KEEP)
//OUT       DD DSN=ROOTABC.TOP80OUT.TXT,DISP=OLD
//*

OPTIONS PAGENO=1 LINESIZE=256 PAGESIZE=32767 NOCENTER;

DATA PSDBIN;
INFILE PSDB;
INPUT
     @  9  PARTNUMB $CHAR25.
     ;
     IF PARTNUMB = '123456';

DATA TOP80OUT;
  SET PSDBIN;
  FILE OUT;
  PUT
     @  1  PARTNUMB $CHAR25.
     ;

 /****************************************************************/
//FTPSTEP  EXEC CCFTPP00
//INPUT   DD *
192.168.1.1
USERNAME
PASSWORD
CD PATH\TO\DIRECTORY
PUT 'ROOTABC.TOP80OUT.TXT' TOP80OUT.TXT
RUN;
[COLOR=red]
//EMAILSTP  EXEC SAS
//SAS.WORK  DD    SPACE=(CYL,(250,150))
//*

FILENAME MAIL EMAIL "TEST@MYEMAIL.COM"
         SUBJECT="JOB HAS FINISHED"
         ATTACH=(OUT EXT='TXT');

DATA _NULL_;
    FILE MAIL;
    PUT "JOB HAS FINISHED";
RUN;[/color]

You probably won't need the SAS.WORK (at least not that volume), but left it in anyway.

The main thing is that you have to be running SAS in order for SAS run your program.

HTH,
Larry

 

[IMDKY]

Hmmm,
Here's the error that I'm showing

ERROR: ERROR IN THE FILENAME STATEMENT.
ERROR: ERROR IN THE FILENAME STATEMENT.
ERROR: ERROR IN THE FILENAME STATEMENT.
ERROR 24-2: INVALID VALUE FOR THE ATTACH OPTION.
ERROR 24-2: INVALID VALUE FOR THE ATTACH OPTION.
ERROR 24-2: INVALID VALUE FOR THE ATTACH OPTION.

 

[LarrySteele]

I had presumed SAS would replace OUT with the file name, but that appears to be what it's complaining about. Try replacing OUT with 'U00JBK7.FILENAME.TXT' (include the single-quotes).

 

[IMDKY]

Sweet! It looks like it works!

It attaches my file as filout.txt.txt I have so many questions now:

If I wanted to get rid of the attachment I can just comment that attachment part out, right?

What do I do if I want to get rid of the extra .txt?

Is there a way to make the attachment .xlsx?

 

[LarrySteele]

Great to hear you got it working.

If I wanted to get rid of the attachment I can just comment that attachment part out, right?

If you take out the ATTACH statement, SAS will send an email w/o and attachment.

What do I do if I want to get rid of the extra .txt?

Have you considered taking out the [tt]EXT='TXT'[/tt] and seeing what happens? I never named my datasets with an extension. My file statement would have been...

//OUT       DD DSN=ROOTABC.TOP80OUT,DISP=OLD

Is there a way to make the attachment .xlsx?

AFAIK, you can't create an .xlsx file. However, you can create an .xls file. Here's a couple of threads that might help point you in the right direction: thread376-1355208: Formatting cells in ODS HTML and thread376-1335588: export to multiple excel tabs. You won't find much in the first, but the second one has a lot of info between Chris and I. Lastly, there's thread376-1381319: Building a string in a loop. While this was not about ODS CHTML, you can see an implementation of it.

If you have questions about ODS, you should start a new thread. Make sure to let everyone know what version of SAS you're using and that you're working with SAS on the mainframe.

Good luck with your project!

 

[IMDKY]

That's awesome! Thanks for the guidance!

 

[LarrySteele]

Glad to help