topic Re: How to autorefresh Excel based on SAS generated csv file? in SAS Programming

How to autorefresh Excel based on SAS generated csv file?

afiqcjohari — Wed, 15 Mar 2017 09:38:08 GMT

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?

Re: How to autorefresh Excel based on SAS generated csv file?

RW9 — Wed, 15 Mar 2017 09:56:08 GMT

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.

Re: How to autorefresh Excel based on SAS generated csv file?

gamotte — Wed, 15 Mar 2017 10:22:18 GMT

Instead of CSV, you could generate html which can be opened in excel.

Re: How to autorefresh Excel based on SAS generated csv file?

afiqcjohari — Wed, 15 Mar 2017 10:22:30 GMT

I don't think investing my time learning VBA is worthy at this point. Could you please advise whether similar template can be generated in SAS?

Re: How to autorefresh Excel based on SAS generated csv file?

RW9 — Wed, 15 Mar 2017 10:29:09 GMT

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.

Re: How to autorefresh Excel based on SAS generated csv file?

gamotte — Wed, 15 Mar 2017 11:21:24 GMT

@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.

Re: How to autorefresh Excel based on SAS generated csv file?

RW9 — Wed, 15 Mar 2017 11:31:43 GMT

Yes, DDE is still around, however it is decades old from being supported, and doesnt work in some setups. I wouldn't recommend it.

Re: How to autorefresh Excel based on SAS generated csv file?

ballardw — Wed, 15 Mar 2017 19:37:54 GMT

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.

Re: How to autorefresh Excel based on SAS generated csv file?

afiqcjohari — Thu, 16 Mar 2017 03:11:23 GMT

The CSV data is the output of SAS scripts. So the process uses SAS to calculate the raw numbers and output this out into a CSV file.

The idea is to automate the whole process using mainly SAS. If this requires VBA, I think a copy and paste is much faster. Could be wrong as I haven't coded in VBA for years.