03-15-2017 05:37 AM - edited 03-15-2017 05:38 AM
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?
03-15-2017 05:56 AM
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:
You can overwrite data in place or process in code.
03-15-2017 06:22 AM
03-15-2017 06:29 AM
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.
03-15-2017 07:21 AM
@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.
03-15-2017 07:31 AM
Yes, DDE is still around, however it is decades old from being supported, and doesnt work in some setups. I wouldn't recommend it.
03-15-2017 11:11 PM
03-15-2017 03:37 PM
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.