SAS DDE Export Excel blank spaces

Question

I have 20 SAS datasets and need to export them to different excel sheets. I have created a SAS macro to do this job. However, I have noticed a blank space at the end in the output.

For eg: "Creative" is getting exported as "Creative ".

As such, there is no blank space in the actual data and is appearing only after exporting the variables to excel. I have still included the following code to get rid these blanks

Filename out dde "excel|[Output.xlsm]!sheet1!r1c1:r10c25";

data want;
 set have;
 array vars (i) _char_;
 do i=1 to dim(vars);
  vars(i)=compress(vars(i));
end;
run;
data _null_;
 Set want;
 file out lrecl=1000 notab;
 put
 var1-var25;
run;

I appreciate your help.

Post the code actually used including export portion and DDE connection string. — Reeza

data _null_ data _null_ · Accepted Answer · 2017-02-08T14:56:59

I was able to reproduce the trailing blank in each field with this. The code you posted did not work starting with missing data HAVE and mixing implicit and explicit arrays. Even it the step with COMPRESS did work it would not fix the problem.

data _null_;
   Set sashelp.shoes(keep=_char_ obs=19);
   file out notab;
   put (_char_)('09'x);
   run;

If you modify the PUT and FILE statement slightly you should get he correct output.

data _null_;
   Set sashelp.shoes(keep=_char_ obs=19);
   file out dlm='09'x notab;
   put (_char_)(:);
   run;

Notice delimiter for list put is changed to tab and the format list in the PUT statement is changed to : from '09'x. I'll leave it to you to research the nuances of LIST PUT.

SAS DDE Export Excel blank spaces

1 Answers