dde function issue while using character variable

[SAS-AtoZ]

sas DDE function provide an unexpected result when character variable used in the put statement. This unexpected result occure only when the character string contain more than one word.(Eg: Employee_name = "Ravi kumar". Word "Ravi" appeared in the Employee_Name column and "Kumar" appeared in the next column in the excel output. ) . How to resolve this issue?

Please see the code below.

data abc;
input age 1-2 salary 4-8 name $ 10-25 ;
cards;
35 60000 siva chnd guva
38 50000 manu gede ali
;
run;

%macro automation(sht,ds,r1,c1,r2,c2,var,cond);
filename ciprod dde "excel|C:\OFFICE\RRS\[difference.xlsx]&sht.!r&r1.c&c1.:r&r2.c&c2.";
data _null_;
file ciprod;
set &ds.;
put &var.;
&cond.;
run;
%mend automation;

%automation(Employee_Data,abc,2,1,3,3,age name salary,)

DDE Result

----------------

Age	Name	Salary
35	siva	chnd
38	manu	gede

I need all the words in the variable "name" should be shone under Name column in the excel output. 

[Patrick]

Don't use DDE unless you have a very very good reason for it. 

https://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/ 

There are many other ways how SAS can write to Excel

[FreelanceReinh]

Hello @SAS-AtoZ and welcome to the SAS Support Communities!

It's long ago that I used DDE (and I don't have Excel installed on my SAS workstation, so cannot test your code), but I remember that I routinely used the NOTAB option (of the FILE or FILENAME statement) in conjunction with explicit tabs ('09'x) in the PUT statement writing to an Excel sheet. It looks like the example Using the NOTAB Option with DDE in the documentation describes this technique and specifically deals with the problem (of embedded blanks in strings) you've encountered.

[Reeza]

Yeah, this is a known issue with DDE and Excel and if you search you'll find examples of the solution on here.

[SAS-AtoZ]

Hi,

I used NOTAB, DDE and '09'x but nothing workied for me.

[FreelanceReinh]

---

@SAS-AtoZ wrote:

I used NOTAB, DDE and '09'x but nothing workied for me.

---

How strange. For me, this worked for many years, from SAS 6.12 (1998) through SAS 9.2 (2012) and with a variety of Windows and Excel versions.

Can you provide more details about how it didn't work for you so that we have a chance to help you further? For example, please post the log using the {i} button, including the code you've used. If the results are different from what you expected then describe the differences.

[SAS-AtoZ]

sorry guys.. i have given NOTAB in the DDE statement and used '09'x only for the truble making character variable. there i got mistaken.

after i gave '09'x after every variable listed in the put statement.. correct result generated...

data abc;
input age 1-2 salary 4-8 name $ 10-25 ;
cards;
35 60000 siva chnd guva
38 50000 manu gede ali
;
run;

%macro automation(sht,ds,r1,c1,r2,c2,var,cond);
filename ciprod dde "excel|C:\OFFICE\RRS\[difference.xlsx]&sht.!r&r1.c&c1.:r&r2.c&c2." NOTAB;
data _null_;
file ciprod;
set &ds.;
put &var.;
&cond.;
run;
%mend automation;

%automation(Employee_Data,abc,2,1,3,3,age '09'x name '09'x salary,)

Above code worked as expected. thank you @FreelanceReinh and @Reeza fpor your help and support.

[Reeza]

Post your code, it works for others so either: 1) You didn't specify it correctly, or 2) Your data has some complexity that causes the issue ('0A'x) is a common issue. If you post your code we can determine which is the issue.