turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Outputting to Excel

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2009 06:51 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

07-23-2009 07:58 AM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

07-23-2009 08:30 AM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Oleg_L

07-23-2009 09:17 AM

Perfect!

Thanks very much for your help!

J

Thanks very much for your help!

J

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

09-22-2009 02:27 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to er_awasthi

09-22-2009 06:23 AM

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.

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

Scott Barry

SBBWorks, Inc.