Help using Base SAS procedures

exporting into Excel loses SUM

Reply
Super Contributor
Posts: 401

exporting into Excel loses SUM

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

Regular Contributor
Posts: 233

Re: exporting into Excel loses SUM

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
stataddTotal
MARRIEDProvince1852.04
SINGLEProvince2002.04
Grand Total3854.08

which is working fine

Super Contributor
Posts: 401

exporting into Excel loses SUM

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.

Frequent Contributor
Frequent Contributor
Posts: 83

exporting into Excel loses SUM

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.

Super Contributor
Posts: 401

exporting into Excel loses SUM

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

PROC Star
Posts: 7,467

exporting into Excel loses SUM

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

264

Ask a Question
Discussion stats
  • 5 replies
  • 178 views
  • 0 likes
  • 4 in conversation