BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
Oleg_L
Obsidian | Level 7
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
Obsidian | Level 7
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
Not applicable
Perfect!

Thanks very much for your help!

J
er_awasthi
Calcite | Level 5
I have one question .how can I output a SAS data set in MVS(Mainframe) to excel sheet . ?
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1349 views
  • 0 likes
  • 4 in conversation