The SAS Output Delivery System and reporting techniques

Export SAS data to Excel

Reply
Contributor
Posts: 38

Export SAS data to Excel

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;

Super User
Posts: 3,250

Export SAS data to Excel

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

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

Contributor
Posts: 38

Export SAS data to Excel

Thanks. Got it.

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

Contributor
Posts: 38

Export SAS data to Excel

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:@'};

SAS Super FREQ
Posts: 8,861

Export SAS data to Excel

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

Contributor
Posts: 38

Export SAS data to Excel

Posted in reply to Cynthia_sas

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

Thanks.

PROC Star
Posts: 7,467

Export SAS data to Excel

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;

Contributor
Posts: 38

Export SAS data to Excel

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');

SAS Super FREQ
Posts: 8,861

Re: Export SAS data to Excel

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


yellow_background.jpg
Contributor
Posts: 38

Export SAS data to Excel

Posted in reply to Cynthia_sas

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.

Contributor
Posts: 38

Export SAS data to Excel

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.

SAS Super FREQ
Posts: 8,861

Re: Export SAS data to Excel

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};

OR

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

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

cynthia

Ask a Question
Discussion stats
  • 11 replies
  • 454 views
  • 0 likes
  • 4 in conversation