SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Best way to put data from sas into pre-formatted Excel xslx file automatically

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Best way to put data from sas into pre-formatted Excel xslx file automatically

Hi all,

I'm relatively new with SAS.  I've got a process right now where I export a SAS data set as a CSV then open the CSV with Excel and copy the data into an existing XLSX file that is pre-formatted to look pretty.  The whole process is essentially done in SAS minus the cutting and pasting so I'm trying to figure out a way to automate the step I described above so that I can have this SAS process flow just be scheduled to run every month. 

My data set consists of about 30 fields (mostly character, dates, numerics) , and only about 2000 rows though that should grow gradually.  I essentially want to be able to paste the whole sas table, headers and all, into an upper left most cell in the xlsx file.  Attached is a crude example.  The My Report header doesnt need to cover each column, so essentially I'd like to have SAS paste the results starting in cell A3 (the cell containing name) everytime.  I'm working on a 64 bit SAS system, with SAS Enterprise Guide 7.1 (though will be updating to 9.4 soon).  I have a 32 bit Excel system.

What I've tried:

I tried exporting to a specific sheet in excel then just putting formulas in my preformatted sheet to reference the data sheet, when I ran this export code it worked in SAS but the xlsx file was corrupted when I opened it.

I looked into using DDE's but I read they are very outdated now.  I also couldn't figure out how to even open an excel file with the DDE.  I tried a statement like:

 

X "my excel file path";

and even

X "start excel";

when I run the first statment I have no errors but nothing happens, when I run the second statement, my program just keeps "running" without end and I have to kill the whole SEGuide process.  Any idea why this isn't working?  Or, is this even the best way to approach this problem?

Thanks for the help

 


Capture.PNGCapture.PNG

Accepted Solutions
Solution
‎07-10-2017 10:11 AM
PROC Star
Posts: 7,363

Re: Best way to put data from sas into pre-formatted Excel xslx file automatically

I'm biased regarding this topic, but see if the following macro does what you need: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
‎07-10-2017 10:11 AM
PROC Star
Posts: 7,363

Re: Best way to put data from sas into pre-formatted Excel xslx file automatically

I'm biased regarding this topic, but see if the following macro does what you need: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 3,105

Re: Best way to put data from sas into pre-formatted Excel xslx file automatically

[ Edited ]

DDE is not available with Enterprise Guide working with a remote SAS server as Excel cannot be started in a remote session without a display to interact with.

 

Check out @art297's option. You could also consider writing to a plain Excel sheet from where an Excel VBA macro can pick up the values to populate the custom template.

 

You might also want to check out the ODS Excel and Excel tagset options in combination with PROC REPORT for fancy formatting. Please bear in mind though you will be constructing your spreadsheet from scratch with these options and the learning curve for this is pretty substantial.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 357 views
  • 1 like
  • 3 in conversation