SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

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

Reply
N/A
Posts: 0

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

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?
Respected Advisor
Posts: 3,768

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

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_;
N/A
Posts: 0

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

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?
Respected Advisor
Posts: 3,768

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

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]
Post a Question
Discussion Stats
  • 3 replies
  • 1004 views
  • 0 likes
  • 2 in conversation