credit card numbers

[deleted_user]

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

[Chevell_sas]

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;

[deleted_user]

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

[ChrisJones]

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.

[Kathryn_SAS]

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

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

[deleted_user]

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

[Chevell_sas]

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.

[deleted_user]

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

[deleted_user]

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