topic Re: How to remove trailing blanks when exporting data from SAS to excel? in Microsoft Integration with SAS

How to remove trailing blanks when exporting data from SAS to excel?

deleted_user — Wed, 25 Mar 2009 10:06:05 GMT

I had a question regarding exporting data from Windows SAS to excel.
I am using a DDE statement to export the data to excel from SAS, but what is happening is, when the data is getting exported, there is a trailing blank space after the value.
For eg: "Shalini" is getting exported as "Shalini ".
I have confirmed that there are no trailing blank spaces in the raw data, this problem occurs only when the exporting is happening.

Do you know how I could avoid this, or why this could be happening.

Also, in DDE is there a possibility of exporting data into a defined named range instead of a range of cells?

Re: How to remove trailing blanks when exporting data from SAS to excel?

data_null__ — Wed, 25 Mar 2009 11:32:04 GMT

If you could show your code that would be helpful. However I was able to produce similar result using NOTAB and LIST put. Try adding a pointer control +(-1) after each variable before you write the tab. Similar to code below.

[pre]
/*triplet = 'excel|[Book1]sheet1!r1c1:r19c7';*/
triplet = 'excel|[Book1]sheet1!print_area';
file dummy2 dde filevar=triplet notab;
do until(eof);
set sashelp.class end=eof;
weight = weight*1000;
put name (sex--weight)(+(-1) '09'x);
end;
[/pre] Write to named range PRINT_AREA

Message was edited by: data _null_;

Re: How to remove trailing blanks when exporting data from SAS to excel?

deleted_user — Wed, 25 Mar 2009 12:09:39 GMT

This is the code that i modified after you recommended using a pointer:
**************************************************************
filename stat_test dde 'excel|D:\[test.xls]UI_2A_raw!r8c22:r12c30' notab;

data _null_;
set sasdata.&outpds._stat_test ;
file stat_test;
put (response_codes nation &area.) (+(-1) '09'x);
run;
***************************************************************

But this still did not help, am I doing something wrong?

Re: How to remove trailing blanks when exporting data from SAS to excel?

data_null__ — Wed, 25 Mar 2009 13:29:14 GMT

What is the value of &AREA.

> put (response_codes nation &area.) (+(-1) '09'x);

This is NOT like the PUT statement in my last post.

[pre]
put response_codes (nation &area.) (+(-1) '09'x);
[/pre]