exporting into Excel loses SUM

[podarum]

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

[Hima]

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

[podarum]

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.

[Mit]

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.

[podarum]

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

[art297]

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

2⁶⁴