sas convert excel to pdf

[amar8581980]

hi everyone, i have excel file on server where i dump my data. below is the code...now is there any way within sas that i can convert this excel file into pdf and send out in email

any help or suggestions would be great

x cp /data/prod_documents/test.xlsx %sysfunc(pathname(work))/test..xlsx;

proc export data=WORK.RETAIL
outfile="%sysfunc(pathname(work))/test..xlsx"
dbms=xlsx
replace;
sheet="RETAIL";
run;

[PaigeMiller]

Excel is usually data in "cells", pdf is generally text and graphics. What is the desired content and appearance of the conversion from Excel to PDF?

[amar8581980]

so the users just want the raw data excel to be converted to pdf....and send out as an attachment

[Tom]

---

@amar8581980 wrote:
so the users just want the raw data excel to be converted to pdf....and send out as an attachment

---

So nothing to do with the code you posted?

The only way you can do that with just SAS is to convert the sheet in the XLSX workbook into a dataset and then print the dataset.  You could use the XLSX engine.

libname myfile xlsx 'test1.xlsx';
ods pdf file='myfile.pdf';
proc print data=myfile.RETAIL;
run;
ods pdf close;

Then just use the normal email processes to mail the PDF file.

[Tom]

So you want to print the work dataset RETAIL that you used create the sheet RETAIL in the XLSX file to a PDF file?

Just use PROC PRINT or PROC REPORT.

ods pdf file='myfile.pdf';
proc print data=retail;
run;
ods pdf close;

[amar8581980]

no i dont want to convert retail dataset in pdf

but this excel file

%sysfunc(pathname(work))/test..xlsx

[ballardw]

If you want to convert an EXCEL file to PDF then use EXCEL tools. Not SAS. SAS works with the data sets to create output like Excel OR PDF.

For SAS to manipulate Excel it basically reads it for the data and uses that data. So there is nothing gained trying to force SAS to do something it is not designed to do.

Look at the ODS PDF output for the data set you "exported" to Excel. See what differences there might be. The main one would relate to the number of columns as PDF will use a printer page size to display columns and if the the page isn't wide enough break up the output into paper width chunks.

PDF also has a page derived line limit that Excel doesn't. If you need to mimic that Excel behavior you may have to define a very large PAPERSIZE setting to allow such.

[Kurt_Bremser]

In your code, you export a dataset to a spreadsheet. instead of doing this, use ODS PDF and a printing procedure. 

[amar8581980]

@Kurt_Bremser 

yes exactly because after the data dump in my excel 'Retail' tab, i have another tab with fancy formatting that using mapping into the retail tab to make numbers look fancy. and hence i want that excel to be converted into pdf

[Kurt_Bremser]

Then do the formatting in SAS, using the tools which SAS provides.

Or try to automate the Excel-to-PDF conversion in Excel with VBA.

[ballardw]

---

@amar8581980 wrote:

@Kurt_Bremser 

yes exactly because after the data dump in my excel 'Retail' tab, i have another tab with fancy formatting that using mapping into the retail tab to make numbers look fancy. and hence i want that excel to be converted into pdf

---

Do you have any idea of how many ways SAS Proc Report, Tabulate or Print can "make numbers look fancy"?

Once upon a time I inherited a bunch of reports that used Excel data on one sheet and "fancy" formatting on another. And then the boss wanted a change to some elements reported.

After I found a number of buried errors in a number of cells that "make the numbers look pretty", actually graphs, and fixed that stuff plus the additions the boss wanted I had about two weeks of work.

Starting from scratch I could make the same reports and graphs in SAS in about 4 hours. And don't have to look for silly formulas someone introduced where they modified an "annual" report to a quarterly one by dividing some numbers by 4.