topic Export SAS data to Excel in ODS and Base Reporting

Export SAS data to Excel

c8826024 — Thu, 27 Oct 2011 20:18:33 GMT

I use tagset to generate a report and convert the report to an Excel spreadsheet. But there is a character variable, which is 19 digit. When the report convert to excel, it is automatically set the last 4 digit to 0000. Anybody know how to fix it?

Sample data:

country account #

US 1234567891234567899

UK 9876543219876543219

....

wrong output in Excel:

country account #

US 1234567891234560000

UK 9876543219876540000

Code:

ods tagsets.excelxp path='/path/' file="myreport.xls";

ods tagsets.excelxp options(sheet_interval='bygroup');

proc report data=mydata

by country;

column account#;

define account#/display 'Account #' f=$char19. style(column)={tagattr="###################"};

run;

Export SAS data to Excel

SASKiwi — Thu, 27 Oct 2011 20:39:51 GMT

Try this. BTW you would have found many postings on this topic if you had searched the forum

define account / display format = $char19. style=[tagattr='format:@'];

Export SAS data to Excel

c8826024 — Thu, 27 Oct 2011 20:50:28 GMT

Thanks. Got it.

I was figuring somebody might already post the solution somewhere. But I was in a hurry. Thanks again.

Export SAS data to Excel

c8826024 — Fri, 24 Feb 2012 15:05:31 GMT

It was working in Proc Report but NOT in proc print. anybody know why?

proc print data=mydata noobs;

var cust_id / style={background=lightyellow font_size=1.5 tagattr='format:@'};

Export SAS data to Excel

Cynthia_sas — Fri, 24 Feb 2012 15:11:26 GMT

Your PROC PRINT syntax is wrong. It should be

var cust_id / style(data)={....};

If that doesn't work, then you should open a track with Tech Support. Assuming that you are using TAGSETS.EXCELXP and TAGATTR for both PROC PRINT and PROC REPORT steps, I would expect that both should work, when the syntax is correct.

PROC REPORT needs style(column) and

PROC PRINT needs style(data).

cynthia

Export SAS data to Excel

c8826024 — Fri, 24 Feb 2012 21:01:35 GMT

Still not working. I will file a ticket to Sas tech support.

Thanks.

Export SAS data to Excel

art297 — Fri, 24 Feb 2012 21:09:21 GMT

Did you define an ODS destination? Seems to be working for me:

ods pdf file='c:\art\testprint.pdf';

proc print data=sashelp.class noobs;

var name / style(data)=

{background=lightyellow font_size=2.5 tagattr='format:@'};

run;

ods pdf close;

Export SAS data to Excel

c8826024 — Fri, 24 Feb 2012 21:14:57 GMT

I use the following code:

ods listing close;

ods tagsets.ExcelXP path='/sas/mydirectory/' file="myreport.xls";

ods tagsets.ExcelXP options(sheet_interval='bygroup' sheet_label='' suppress_bylines='yes' autofit_height='yes');

Re: Export SAS data to Excel

Cynthia_sas — Fri, 24 Feb 2012 21:29:15 GMT

Hi,

I tried a variation of Art's code and it worked for me, too. See below:

proc sort data=sashelp.class out=class;

by age;

where age le 13;

run;

ods listing close;

ods tagsets.ExcelXP path='c:\temp\' (url=none)

style=sasweb file="myreport.xls";

ods tagsets.ExcelXP options(sheet_interval='bygroup'

sheet_label='' suppress_bylines='yes'

autofit_height='yes');

proc print data=class noobs;

by age;

var name age/ style(data)=

{background=lightyellow font_size=10pt tagattr='format:@'};

var height weight sex /

style(data)={background=cyan};

run;

ods _all_ close;

And AGE is treated as a character (even though it's a number -- you can tell by the green triangle in each cell). Plus, the formatting got applied - -as far as colors for the cells that I specified.

See screen shot.

cynthia

Export SAS data to Excel

c8826024 — Mon, 27 Feb 2012 19:36:03 GMT

Will different SAS version making a difference?

The code is working fine when I run it on 9.2 (TS2M0) but is NOT working when I run it on 9.1.3 SP 4.

Export SAS data to Excel

c8826024 — Tue, 28 Feb 2012 13:52:19 GMT

I think it is the problem of version 9.1. I upgrade my software to 9.2 and the problem is gone even without style(data).

Thanks everyone.

Re: Export SAS data to Excel

Cynthia_sas — Tue, 28 Feb 2012 14:15:33 GMT

Hi:

More likely it was the fact that the default tagset template for ExcelXP was different in 9.1.3 versus 9.2 -- and what you tried to do worked better with the 9.2 template code.

Using the report location for PROC PRINT and PROC REPORT ( such as style(data) or style(header) or style(column) is considered a best practice) -- since each REPORT and PRINT statement allows you to to alter the header cells and the data cells, it is better to be explicit. For example, it's probably inappropriate to apply a numeric Microsoft format to the column header and equally inappropriate to use the header cell colors for the data cells. So something like this is recommended -- no matter what destination you use:

var age height / style(data)={font_size=10pt just=l}

style(header)={background=pink just=c};

DEFINE AGE / style(column)={font_size=10pt just=l}

style(header)={background=purple just=c};

cynthia