Emailing proc export

[Q1983]

PROC EXPORT OUTFILE='\\my path\\Capital_Markets_Report_%sysfunc(today(), MMDDYY7.).xlsb'   

           DATA= FINAL

                label

            DBMS= EXCELCS REPLACE;

            SHEET='CMREPORT';

            SERVER='saspcff';

run;

%let OUTFILE1 = '\\my path\';

%let reportout=Capital_Markets_Report_%sysfunc(today(), MMDDYY7.).xlsb;

filename MI_email email

TO= ("myemail@myco.com") 

SUBJECT= "Capital Markets Report &rpt_date." 

attach = "&OUTFILE1/&Reportout..xlsb";

ERROR: Error opening attachment file '\\mypath\'/Capital_Markets_Report_ 072020.xlsb.xlsb.

ERROR: Physical file does not exist, /sas/config9_4/compute/Lev1/SASApp/\\mypath\.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

The proc export works fine.  It populated to the path and folder (name changed to hid company name)

However  also want to email the same report called Capital markets to an email recipient.  I get the above named error.  It appears the slashes get crossed (//  \\).  Is that the reason for the errors?????

[PaigeMiller]

%let OUTFILE1 = '\\my path\';

Do not use quotes here.

The quotes wind up in the path/file name as shown in the ERROR message

'\\mypath\'/Capital_Markets_Report_ 072020.xlsb.xlsb.

which is not a valid path name. Can you see why this is not a valid path name? What would the correct path/file name be? — please include that in your reply.

Also in some cases you have a forward slash and other cases you have a backward slash and that's not going to work either. Your macro variables have to resolve to legal path/file name, and you need to pay attention to those details.

[SASKiwi]

The evidence in your SAS log suggests you are writing the Excel spreadsheet to a Windows file server folder, then trying to read that folder from your Unix SAS server. The likely cause of your problem is your SAS server doesn't have the correct permissions to read that location although here - attach = "&OUTFILE1/&Reportout..xlsb" - you should be using a back slash not a forward slash.

Since you are using the SAS PC File Server to create the spreadsheet OK, it has the permissions to write to the Windows file server folder, but your Unix SAS server probably doesn't. You will need to work with your IT/SAS administrators to sort this.

[ballardw]

From

ERROR: Error opening attachment file '\\mypath\'/Capital_Markets_Report_ 072020.xlsb.xlsb.

you need to look very closely at how/why you add two xlsb

in

%let reportout=Capital_Markets_Report_%sysfunc(today(), MMDDYY7.).xlsb;

attach = "&OUTFILE1/&Reportout..xlsb";

And similar how OUTFILE1 messes with things by including quotes as part of its value, not to mention changing directions of slashes

You really should use the same code to build the name of the OUTFILE in proc export and your Attach string.

[Tom]

How is the server where SAS is running going to read the file that you wrote on the server 'saspcff'?

Do you have the share "\\my path" mounted somewhere on the Unix filesystem that the SAS server is using?

[Kurt_Bremser]

Use a location that's always available on a UNIX system, a properly structured date (YMD sorts properly, MDY does not), and the much easier to handle XLSX engine (which can be used on all SAS platforms):

%let reportout=Capital_Markets_Report_%sysfunc(today(),yymmddn8.).xlsx;
%let rpt_date=%sysfunc(today(),mmddyy10.);

proc export
  data=final
  outfile="$HOME/&outfile."   
  label
  dbms=xlsx
  replace
;
sheet = 'CMREPORT';
run;

filename MI_email
  email
  to=("myemail@myco.com") 
  subject="Capital Markets Report &rpt_date." 
  attach = "$HOME/&reportout."
;