07-26-2015 02:35 PM
Hello everyone. I am trying to get data from SAS into excel using DDE (I cannot use Proc export for a couple of reasons).
Anyways I need code to open excel, rename a given tab to a specific value, and then to export some column headers, and then the data. The problem i'm running into are renmaing the tab, exporting the column headers and saving the file.
Problem #1: how can you get sas to add a new tab to a excel file, and delete the three default tabs (sheet1,2,3) using DDE?
problem #2: SAS DDE can't seem to export columns that have spaces in them. For example when I do....
options xwait noxsync;
x '"C:\Program Files\Microsoft Office\Office14\excel.exe"';
filename random dde
put one two three;
I get an excel file with the column names of
LN_ID REO ID Unit #
So I get 5 column headers and the "REO ID" colum name is split into two columns... What option do i need to specify to make sure this doesn't happen?
Question 3: Assuming I can get the column headers to write correctly, how would I then save this Excel sheet + tab to a new excel file?
If anyone is curiuos I can't use proc export for the following reason...
My third column name has "UNIT #" in it, and for whatever reason sas is converting the "#" to a "." so I am getting "unit .". This is happening even with the following options.....
proc export data=WORK.BILLINGFLATFEEREPORT outfile="c:\users\mydocs\myfile.xls"
I have no idea why validvarname isn't allowing the export to work correctly (I am even using LABEL and the label value is "Unit #". However this is due in a day so i gave up on proc export.
Any and all help would be appreciated!
07-26-2015 03:51 PM
I used the notab and lrecl option on my filename to resolve the issue:
filename random dde 'excel|Sheet1!r1c1:r100c100' NOTAB LRECL=1000;
07-26-2015 03:53 PM
Haha I know the feeling. I actually just stuck to proc export, and wrote some vba code to dynamically open the files and re-name the columns, and then had sas call the macro.... I hate that solution tho so i'm going to try yours super fast :-)