The SAS Output Delivery System and reporting techniques

credit card numbers

Reply
N/A
Posts: 0

credit card numbers

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 !)
SAS Employee
Posts: 88

Re: credit card numbers

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;
N/A
Posts: 0

Re: credit card numbers

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
Occasional Contributor
Posts: 9

Re: credit card numbers

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.
SAS Employee
Posts: 22

Re: credit card numbers

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

http://support.sas.com/rnd/base/topics/odsmarkup/
N/A
Posts: 0

Re: credit card numbers

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
SAS Employee
Posts: 88

Re: credit card numbers

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.
N/A
Posts: 0

Re: credit card numbers

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
N/A
Posts: 0

Re: credit card numbers

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
Ask a Question
Discussion stats
  • 8 replies
  • 967 views
  • 0 likes
  • 4 in conversation