DATA Step, Macro, Functions and more

How to autorefresh Excel based on SAS generated csv file?

Reply
Frequent Contributor
Posts: 99

How to autorefresh Excel based on SAS generated csv file?

[ Edited ]

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:

 

csv simple.PNG

 

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

 

excel template to fill.PNG

 

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?

Super User
Super User
Posts: 7,401

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

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.

Frequent Contributor
Posts: 99

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

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?
Super User
Super User
Posts: 7,401

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

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.

Regular Contributor
Posts: 194

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

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

Super User
Super User
Posts: 7,401

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

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

Frequent Contributor
Posts: 99

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

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.

Regular Contributor
Posts: 194

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

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

Super User
Posts: 10,487

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

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.

Ask a Question
Discussion stats
  • 8 replies
  • 131 views
  • 0 likes
  • 4 in conversation