DATA Step, Macro, Functions and more

Outputting to Excel

Reply
N/A
Posts: 0

Outputting to Excel

Hello. I use the following code to write out 4 columns from a sas dataset (named "approval") into Excel.

filename data dde "excel|Data!r2c1:r65536c4";
data _null_;
file data notab;
set approval;
put lsrpid '09'x
lsmfid '09'x
aeantx '09'x
ltmfrn '09'x;
run;

The first two fields are numeric; the last two are character. There are a few issues when the fields are written into Excel:

1) The character fields all get an extra space added on the end, I don't want the extra space
2) If the character string is empty SAS outputs 2 spaces, I want the cell to be empty
3) If the numeric value is missing SAS outputs a ".", I want the cell to be empty

Can anyone help?
Thanks
James
Regular Contributor
Posts: 151

Re: Outputting to Excel

Posted in reply to deleted_user
Hi!
Try this code.

data approval;
infile cards dlm=',' missover;
input lsrpid lsmfid aeantx $ ltmfrn $;
cards;
10, 9, asdsdf,
11, 12, , nnfjkgk
16, ., , ppfdkk
., ., djfu4, kflld
;

filename data dde "excel|Data!r2c1:r65536c10";

data _null_;
file data notab ;
set approval;
if lsrpid=. then put '09'x @; else put lsrpid '09'x @;
if lsmfid=. then put '09'x @; else put lsrpid '09'x @;
if aeantx='' then put '09'x @; else do; do j=1 to length(aeantx);d=Substr(aeantx,j,1);Put d $1.@;end; put '09'x @; end;
if ltmfrn='' then put '09'x @ ; else do j=1 to length(ltmfrn);d=Substr(ltmfrn,j,1);Put d $1.@;end; put '09'x;
run;
Regular Contributor
Posts: 151

Re: Outputting to Excel

Posted in reply to deleted_user
There are some updates in code.

data approval;
infile cards dlm=',' missover;
input lsrpid lsmfid aeantx $ ltmfrn $;
cards;
10, 9, asdsdf,
11, 12, , nnfjkgk
16, ., , ppfdkk
., ., djfu4, kflld
, , ,
66, 79, plf,
89,78,,
;

filename data dde "excel|Data!r2c1:r65536c10";

data _null_;
file data notab ;
set approval;
if lsrpid=. then put '09'x @; else put lsrpid '09'x @;
if lsmfid=. then put '09'x @; else put lsmfid '09'x @;
if aeantx='' then put '09'x @; else do; do j=1 to length(aeantx);d=Substr(aeantx,j,1);Put d $1.@;end; put '09'x @; end;
if ltmfrn='' then put '09'x ; else do; do j=1 to length(ltmfrn);d=Substr(ltmfrn,j,1);Put d $1.@;end; put '09'x; end;
run;
N/A
Posts: 0

Re: Outputting to Excel

Perfect!

Thanks very much for your help!

J
Occasional Contributor
Posts: 16

Re: Outputting to Excel

Posted in reply to deleted_user
I have one question .how can I output a SAS data set in MVS(Mainframe) to excel sheet . ?
Super Contributor
Super Contributor
Posts: 3,174

Re: Outputting to Excel

Posted in reply to er_awasthi
See this document from the SAS support http://support.sas.com/ website, located using the site SEARCH facility, using z/OS instead of MVS (there are other similar documents at the support site that may be helpful to your interest here):

http://support.sas.com/rnd/base/ods/templateFAQ/MVSODS3.pdf


Scott Barry
SBBWorks, Inc.
Ask a Question
Discussion stats
  • 5 replies
  • 176 views
  • 0 likes
  • 4 in conversation