topic Re: Outputting to Excel in SAS Programming

Outputting to Excel

deleted_user — Thu, 23 Jul 2009 10:51:09 GMT

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

Re: Outputting to Excel

Oleg_L — Thu, 23 Jul 2009 11:58:07 GMT

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;

Re: Outputting to Excel

Oleg_L — Thu, 23 Jul 2009 12:30:19 GMT

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;

Re: Outputting to Excel

deleted_user — Thu, 23 Jul 2009 13:17:12 GMT

Perfect!

Thanks very much for your help!

J

Re: Outputting to Excel

er_awasthi — Tue, 22 Sep 2009 06:27:43 GMT

I have one question .how can I output a SAS data set in MVS(Mainframe) to excel sheet . ?

Re: Outputting to Excel

sbb — Tue, 22 Sep 2009 10:23:59 GMT

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.