Outputting to Excel

[deleted_user]

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

[Oleg_L]

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;

[Oleg_L]

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;

[deleted_user]

Perfect!

Thanks very much for your help!

J

[er_awasthi]

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

[sbb]

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.