topic exporting into Excel loses SUM in SAS Procedures

exporting into Excel loses SUM

podarum — Wed, 22 Feb 2012 19:35:34 GMT

Anyone know why and how to fix when I'm exporting from SAS a large file into a .csv (or .txt) and try to pivot it in Excel it loses the ability to Sum up the data. It is still numeric data.

Sample :

2002.04,,SINGLE,,Province1

Thanks

Re: exporting into Excel loses SUM

Hima — Wed, 22 Feb 2012 19:59:49 GMT

If I understand you correctly, this is what I have done.

data test;
input val stat $ add $;
datalines;
2002.04 SINGLE Province1
1852.04 MARRIED Province2
;
RUN;

PROC EXPORT DATA = test
OUTFILE= "C:\Documents and Settings\TEST.XLS"
DBMS=EXCEL REPLACE;
SHEET="Sheet1";
RUN;

Then opened the excel to creat a pivot table.

Sum of val
stat	add	Total
MARRIED	Province	1852.04
SINGLE	Province	2002.04
Grand Total		3854.08

which is working fine

exporting into Excel loses SUM

podarum — Wed, 22 Feb 2012 20:03:40 GMT

Yes, with 1 million records.. when I export into .csv.. then pivot works but not the SUM. In my sample I left a ' ' where the actual number eg 345345 would be and another ' ' where 245345 would be, but in my case I have alot of spaces.. Which I think are the culprit.

exporting into Excel loses SUM

Mit — Thu, 23 Feb 2012 01:43:37 GMT

What is the size of your SAS data?

So far I know if the data is larger than 2Gb then you won't be able to export it to EXCEL or Access. No matter howmany records are there.

exporting into Excel loses SUM

podarum — Thu, 23 Feb 2012 03:15:49 GMT

It's about 63 MB's (in .csv or .txt format) and about 1 million records

exporting into Excel loses SUM

art297 — Thu, 23 Feb 2012 04:00:47 GMT

I can't find it in the Microsoft documentation but, with 64 bit versions, I think that has increased to around

2⁶⁴