DDE Removes Spaces from Text Fields?

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

DDE Removes Spaces from Text Fields?

Hello, All!

I am using DDE to write a report to XL.  When I write out a text field from a SAS file that contains more than one word (e.g. Company A), each word ends up in a different column.  When I put quotes around the text field (e.g. "Company A"), the space between the words ends up being removed (e.g. CompanyA).

Does anyone know a work-around for this?

My code is attached; the XL "template" can be any blank workbook.  The sample code uses the sashelp.cars file.

Many thanks!

Attachment
Attachment
Attachment

Accepted Solutions
Solution
‎07-02-2014 04:30 PM
Super User
Super User
Posts: 6,500

Re: DDE Removes Spaces from Text Fields?

Try making a version of your data where internal spaces are replaced with 'A0'x ?

data new ;

set old;

model = translate(trim(model),'A0'X,' ');

run;

View solution in original post


All Replies
Super User
Super User
Posts: 6,500

Re: DDE Removes Spaces from Text Fields?

No idea.

Try adding this line to the data step that writes the data in %MACRO_SendToXLWithHeadings() .

format _character_ $quote. ;

Contributor
Posts: 27

Re: DDE Removes Spaces from Text Fields?

No joy.  Smiley Sad

OnVirtualMachine.PNG

But it is interesting what happens when I paste the same data here.  Has me thinking that there's some Excel option at work...

MakeModel
AcuraMDX
AcuraRSX Type S 2dr
AcuraTSX 4dr
AcuraTL 4dr
Acura3.5 RL 4dr
Acura3.5 RL w/Navigation 4dr
AcuraNSX coupe 2dr manual S
AudiA4 1.8T 4dr
AudiA41.8T convertible 2dr
AudiA4 3.0 4dr
AudiA4 3.0 Quattro 4dr manual
Solution
‎07-02-2014 04:30 PM
Super User
Super User
Posts: 6,500

Re: DDE Removes Spaces from Text Fields?

Try making a version of your data where internal spaces are replaced with 'A0'x ?

data new ;

set old;

model = translate(trim(model),'A0'X,' ');

run;

Contributor
Posts: 27

Re: DDE Removes Spaces from Text Fields?

My hero!  Thank you!!!  Smiley Happy

Super User
Super User
Posts: 6,500

Re: DDE Removes Spaces from Text Fields?

I suspect that you real problem is some setting in Excel.  For example in the past I have seen Excel automatically convert text that tried to import or paste into columns using the delimiter that I last used to convert using the TEXT_TO_COLUMN functionality.  So instead of pasting on column of strings it split the strings into multiple cells. I am sure it thought it was being "helpful" but it was a pain to get it to stop.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 500 views
  • 1 like
  • 2 in conversation