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
- /
- ODS and Base Reporting
- /
- credit card numbers

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-20-2006 01:12 PM

with 16 digits, excel is unkind to the cc-numbers passed from SAS. Is there some style that could recognize a 16 digit numeric value (or string) and "protect" it in some way that excel would respect? (excel sets the 16th digit to zero !)

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

06-20-2006 03:13 PM

You can use the text format. I am not sure which method you are using to move the output to Excel, however if you are using the ExcelXP tagset the below will work.

data one;

x="12345678912345678";

run;

ods tagsets.excelxp file="temp.xls";

proc print data=one;

var x / style(data)={tagattr="format:@"};

run;

ods tagsets.excelxp close;

data one;

x="12345678912345678";

run;

ods tagsets.excelxp file="temp.xls";

proc print data=one;

var x / style(data)={tagattr="format:@"};

run;

ods tagsets.excelxp close;

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

06-21-2006 05:54 AM

thank you, but.... running your example, my results are not what you intended of that code

Obs x

1 1.23457E+16

is the excel display when I open temp.xls

formatting the "x" column as number with no decimal places..... shows

12345678912345700

the session log shows

NOTE: SAS 9.1.3 Service Pack 3

How can I tell if this is not the latest version of excelXP

Peter

Obs x

1 1.23457E+16

is the excel display when I open temp.xls

formatting the "x" column as number with no decimal places..... shows

12345678912345700

the session log shows

NOTE: SAS 9.1.3 Service Pack 3

How can I tell if this is not the latest version of excelXP

Peter

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

06-21-2006 07:54 AM

Peter,

As you originally suggested, the problem lies with Excel being unable to recognise numbers with more than 15 digits. You will have to ensure that SAS exports the field as a character field (with preceeding quote). If you can achieve that, Excel will correctly read the value as text.

As you originally suggested, the problem lies with Excel being unable to recognise numbers with more than 15 digits. You will have to ensure that SAS exports the field as a character field (with preceeding quote). If you can achieve that, Excel will correctly read the value as text.

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

06-21-2006 07:55 AM

Do you have the latest ExcelXP tagset installed? You can find it at the following link:

http://support.sas.com/rnd/base/topics/odsmarkup/

http://support.sas.com/rnd/base/topics/odsmarkup/

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

06-21-2006 09:24 AM

many thanks, I needed that pointer.

After downloading and running that proc template with the "Excel XP tagset (SAS 9.1.3, v1.37, 05/31/06)", the 16-digit character value successfully surfaces in excel.

How about 16 numeric digits?

I tried this code :

ods tagsets.excelxp file="temp2.xls" ;

proc print data= two;

var x / style(data)={tagattr="format:@"};

var n / style(data)={tagattr="Format:0"} ;

format n z16. ;

run;

ods tagsets.excelxp close;

%sysexec "temp2.xls";

and excel shows

Obs x n

1 12345678912345678 12345678912300000

Clearly somewhere, 12 sig. digits is assumed.

With no more success, I also tried

ods tagsets.excelxp file="temp4.xls" options(Numeric_Test_Format='16.' );

I know there are many things I'm missing here.

What's the magic that allows all 16 digits through ?

Peter

After downloading and running that proc template with the "Excel XP tagset (SAS 9.1.3, v1.37, 05/31/06)", the 16-digit character value successfully surfaces in excel.

How about 16 numeric digits?

I tried this code :

ods tagsets.excelxp file="temp2.xls" ;

proc print data= two;

var x / style(data)={tagattr="format:@"};

var n / style(data)={tagattr="Format:0"} ;

format n z16. ;

run;

ods tagsets.excelxp close;

%sysexec "temp2.xls";

and excel shows

Obs x n

1 12345678912345678 12345678912300000

Clearly somewhere, 12 sig. digits is assumed.

With no more success, I also tried

ods tagsets.excelxp file="temp4.xls" options(Numeric_Test_Format='16.' );

I know there are many things I'm missing here.

What's the magic that allows all 16 digits through ?

Peter

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

06-21-2006 09:41 AM

Excel only maintains 15 significant digits for numbers, so this is a limitation of Excel. If you need more than the 15 characters, the field will need to be read as text.

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

06-21-2006 11:13 AM

thank you - I'm quite happy that the credit card (cc) number must be treated as text.

Does that mean a SAS column containing the cc number as a numeric must be converted to character in the dataset that is read by proc print ?

When I use the text style for numeric data >>>>

ods tagsets.excelxp file="temp3.xls" ;

proc print data= two;

var x n / style(data)={tagattr="format:@"};

format n z16. ;

run;

ods tagsets.excelxp close;

ods _all_ close ;

%sysexec "temp3.xls";

I get text in excel,

Obs x n

1 12345678912345678 1.23456789123E16

but it arrives in sas format best12. even though I requested z16. in the format statement

Peter

Does that mean a SAS column containing the cc number as a numeric must be converted to character in the dataset that is read by proc print ?

When I use the text style for numeric data >>>>

ods tagsets.excelxp file="temp3.xls" ;

proc print data= two;

var x n / style(data)={tagattr="format:@"};

format n z16. ;

run;

ods tagsets.excelxp close;

ods _all_ close ;

%sysexec "temp3.xls";

I get text in excel,

Obs x n

1 12345678912345678 1.23456789123E16

but it arrives in sas format best12. even though I requested z16. in the format statement

Peter

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

06-21-2006 11:24 AM

sorry,

I miss-read "1.23456789123E16" as having best12. format, when it is in fact best16. !

The underlying data was 17 digits !

Revised to 16, it is all working

Obs x n

1 1234567891234567 1234567891234567

Many thanks I now can delive CC-numbers as we need !

Peter

I miss-read "1.23456789123E16" as having best12. format, when it is in fact best16. !

The underlying data was 17 digits !

Revised to 16, it is all working

Obs x n

1 1234567891234567 1234567891234567

Many thanks I now can delive CC-numbers as we need !

Peter