BookmarkSubscribeRSS Feed
c8826024
Calcite | Level 5

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;

11 REPLIES 11
SASKiwi
PROC Star

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

c8826024
Calcite | Level 5

Thanks. Got it.

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

c8826024
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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

c8826024
Calcite | Level 5

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

Thanks.

art297
Opal | Level 21

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;

c8826024
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ

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
c8826024
Calcite | Level 5

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.

c8826024
Calcite | Level 5

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.

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1403 views
  • 0 likes
  • 4 in conversation