BookmarkSubscribeRSS Feed
afiqcjohari
Quartz | Level 8

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?

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

afiqcjohari
Quartz | Level 8
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?
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

gamotte
Rhodochrosite | Level 12

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

afiqcjohari
Quartz | Level 8
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.

gamotte
Rhodochrosite | Level 12

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

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 832 views
  • 0 likes
  • 4 in conversation