Get column names with Spaces into DDE

Super Contributor
Posts: 418

Get column names with Spaces into DDE

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
   'excel|Sheet1!r1c1:r100c100' ;

data _null_;

data hutto;
two="Reo id";
three="Unit #";

data _null_;
  file random;
  set hutto;
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.....

options validvarname=any;

proc export data=WORK.BILLINGFLATFEEREPORT outfile="c:\users\mydocs\myfile.xls"
replace label;

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!

Super User
Posts: 17,829

Re: Get column names with Spaces into DDE

Been there Smiley Happy

I used the notab and lrecl option on my filename to resolve the issue:

filename random dde 'excel|Sheet1!r1c1:r100c100'  NOTAB LRECL=1000;

Super Contributor
Posts: 418

Re: Get column names with Spaces into DDE

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 :-)

Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation