topic Re: credit card numbers in ODS and Base Reporting

credit card numbers

deleted_user — Tue, 20 Jun 2006 17:12:32 GMT

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 !)

Re: credit card numbers

Chevell_sas — Tue, 20 Jun 2006 19:13:03 GMT

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;

Re: credit card numbers

deleted_user — Wed, 21 Jun 2006 09:54:35 GMT

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

Re: credit card numbers

ChrisJones — Wed, 21 Jun 2006 11:54:13 GMT

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.

Re: credit card numbers

Kathryn_SAS — Wed, 21 Jun 2006 11:55:53 GMT

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

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

Re: credit card numbers

deleted_user — Wed, 21 Jun 2006 13:24:45 GMT

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

Re: credit card numbers

Chevell_sas — Wed, 21 Jun 2006 13:41:39 GMT

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.

Re: credit card numbers

deleted_user — Wed, 21 Jun 2006 15:13:21 GMT

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

Re: credit card numbers

deleted_user — Wed, 21 Jun 2006 15:24:32 GMT

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