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