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;
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:@'];
Thanks. Got it.
I was figuring somebody might already post the solution somewhere. But I was in a hurry. Thanks again.
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:@'};
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
Still not working. I will file a ticket to Sas tech support.
Thanks.
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;
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');
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
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.