BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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 !)
8 REPLIES 8
Chevell_sas
SAS Employee
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
Not applicable
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
Calcite | Level 5
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
SAS Employee
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
Not applicable
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
SAS Employee
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
Not applicable
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
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2565 views
  • 0 likes
  • 4 in conversation