I want to send out a monthly report which the numbers are generated by SAS and then PROC EXPORT to a csv file.
The csv file typically looks like below:
However, this is not the final output as there's an Excel file with a nice looking template which looks like this.
The Excel file also contains a formula like MOM% (month over month).
What I want is, to link both. So that, the better looking Excel will automatically fill in the values based on the csv file generated by SAS.
For example, if I run the March numbers, the better looking Excel will then be filled with the new numbers generated by SAS.
Any advice on how to do so?
Then you want a VBA/Excel forum. This has nothing to do with SAS. For your reference, and OnOpen() function can load the data and process it anyhows you like:
First Google result:
http://sitestory.dk/excel_vba/csv-file-import.htm
You can overwrite data in place or process in code.
You have stated that you have a template file that you need to updated with CSV data - at no point in this part of the process anything to with SAS. Hence you would use Excel (and VBA which is a core component of Excel which you should know if you use it - it can even record the code for you, you don't need to know it!) to import and process the data. There is no magic button in SAS to control external applications.
You could of course look at it the other way round, why do you want output like that specifically? You could generate the output Excel file using tagsets.excelxp for instance to get all the formatting and such like, just process the data in SAS, create a dataset which looks like your output file, then proc report to that tagset. This would work fine if its just a plain Excel file as you give, but then you would have the template file, it would be generated from SAS.
Need to clarify what you want exactly, and restrictions.
@gamotte, not sure how exporting to HTML would help any, Excel would still need to open and process the imported data.
@RW9, my point was that the excel template could be replaced by css rules or SAS style
(or using tagsets.excelxp as you propose).
I missed the real time data feed part of the request. I have heard of DDE links but never used them
so i could not tell if that answers the question.
Yes, DDE is still around, however it is decades old from being supported, and doesnt work in some setups. I wouldn't recommend it.
Instead of CSV, you could generate html which can be opened in excel.
Or ODS Tagsets.excelxp creates formatted tables that Excel can open.
or ODS Excel.
Proc report or Proc tabulate could make a table that looks like that without the export and re-read approach. Just write direct to file.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.