CODEWITHSUNDEEP
excelsasspacedde
ckp
ckp

Reputation: 593

SAS DDE Export Excel blank spaces

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.

Upvotes: 0

Views: 909

Answers (1)

data _null_
data _null_

Reputation: 9109

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.

Upvotes: 1

Related Questions