BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kyle_carmody
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

2 REPLIES 2
art297
Opal | Level 21

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

 

SASKiwi
PROC Star

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.

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!

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