Basic question. SAS Enterprise user is fine with export to Excel but wants pretty pdf with logos

[dewsbury]

Hi, this is best explained by a (fake) example.

I am working for an electricity provider.
All our customers billing data is available in SAS enterprise.  I am semi-technical.
Specifically, the data is simply the date and amount of all electricity bills for the last 20 years (similar to a bank statement - one row of data for every bill).

I have no difficult querying this data and exporting to excel.  That where my expertise ends.
I cannot send an excel sheet to a customer - it does not look professional.
Is it possible to generate a pdf with logos, headers and footers ?  The pdf should look like an electricity bill and not an excel file!
How do I do this? What is the best tool from SAS to do this? Can SAS enterprise to it?

Thanks folks!

[Tom]

You can generate any report you want.  You can include images.

Use your favorite reporting tool, probably PROC REPROT.

Use the ODS facility to direct the report to the file you want.

Use ODS PDF to make a PDF file.

Check out the other ODS destinations to see what other types of files you can generate.

[ballardw]

If you have access to your logo or other images you might want to include they may be included as well. The techniques vary a bit depending on actual desired appearance.

If you have an example of what you want something to look like you may get more targeted responses.

[dewsbury]

Can I fine tune my request, please!
I'm trying to regenerate bank statements.
I have 5 million transactions in SAS and want to generate bank statements for 20,000 customers over a 20 year time span. One statement per customer.
From a high level perspective, how do I generate 20,000 excel workbooks - one for each customer. The name of the excel file will be 99999.xlsx where 999 is the customer number from SAS.
Is this pretty straightforward ? A loop thru the data generating 20,000 output files. This will only be run once.
Where do I start? (My current expertise is querying the data but I've no idea how to generate output excel files).
The data structures are simple.

[PaigeMiller]

---

@dewsbury wrote:
Can I fine tune my request, please!
I'm trying to regenerate bank statements.
I have 5 million transactions in SAS and want to generate bank statements for 20,000 customers over a 20 year time span. One statement per customer.
From a high level perspective, how do I generate 20,000 excel workbooks - one for each customer. The name of the excel file will be 99999.xlsx where 999 is the customer number from SAS.
Is this pretty straightforward ? A loop thru the data generating 20,000 output files. This will only be run once.
Where do I start? (My current expertise is querying the data but I've no idea how to generate output excel files).
The data structures are simple.

---

ODS EXCEL with PROC REPORT (or PROC PRINT) and a BY statement (probably BY CUSTOMER_ID; or something like that). ODS EXCEL has a feature (actually an option) that creates a new Excel tab for each value of the BY variable (in this case, each value of CUSTOMER_ID). That's not quite the same as having a separate Excel file, but that could be programmed as well using a macro. So whether it's 5 customers or 20,000 customers, the amount of coding you will have to do is the same; the only real difference is that it takes more computer resources to do this for 20,000 customers. You can tell your boss you are working hard while it is running, even though you're not working hard, the computer is working hard.

[Reeza]

Two references that may be helpful then:

https://github.com/statgeek/SAS-Tutorials/blob/master/SAS_export_multiple_workbook_worksheet.sas

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

First, figure it out for one scenario then scale it. Scaling is trivial for this type of exercise.

[Kurt_Bremser]

Brute force approach:

• Create code for a single customer
• Wrap into a macro, with customer key as parameter
• Create control dataset with unique customer keys
• Run a DATA _NULL_ step from this which uses CALL EXECUTE to call the macro for each customer

[dewsbury]

Thanks ... so, "PROC REPROT" is my starting point.  Time to watch a youtube video explaining syntax etc!

[SASKiwi]

Just curious, but why are you using SAS to do bank statements when banks already have existing statement production facilities?

Also be careful scaling ODS to deal with 5 million transactions. In my experence ODS is very resource intensive and doesn't scale well to very large volumes.

EDIT: Just realised you are dealing with an electricity provider, not a bank, but same issue applies re existing statement production facilities. Don't see why Excel needs to be involved at all if data is being written to PDFs - why not just write direct to a PDF? 

[dewsbury]

Indeed your questions and comments are very valid.
In simple terms my excel/VBA skills are better than my SAS skills.

However, I may rectify that!

Thanks,

[Reeza]

ODS PDF + PROC REPORT or ODS DOCUMENT are good options as well. You actually have quite a few ways to design this. 

This isn't super pretty but it's an example of what you're trying to do:

https://support.sas.com/resources/papers/proceedings17/0801-2017.pdf

And the lazy way, depending on how often you do this. Create an Excel template that can be saved to PDF with the logo, header, formatting manually. 

Link the data from a second spreadsheet in the file.

Use SAS to copy the Excel file and populate the second spreadsheet with the data for each report.

Use SAS + VBS to have the Excel file converted to PDF. 

You would need XCMD and access to run locally to do something like this. The first option is better in the long run for sure but it takes more work to ensure everything is correct and aligned.

[AlanC]

As mentioned, SAS is not the best way to handle. Typing on cell so apologies in advance.

SAS datatsets are ODBC compliant. Download free driver from SAS but it is probably already installed. Use something like PowerBI to read SAS data and format the report. Any report writer can read odbc.

[Ksharp]

Of course you can.

Try PREIMAGE= style.

ods pdf file='c:\temp\temp.pdf';
title '(*ESC*)S={preimage="c:\temp\Ksharp.png"}';
title2 'Whatever Title is ';
proc report data=sashelp.class nowd;
run;
ods pdf close;