topic Re: Dataset too large to export to Excel in SAS Procedures

Dataset too large to export to Excel

Elvin — Tue, 01 Jul 2014 22:55:26 GMT

Hi, I have generated a large SAS dataset, which I want to export to Excel for further use but it have too many records (>65536 rows). My Excel file can only show the data up to 65536 row. What should I do? Or, how can I tell PROC EXPORT to export a certain range of records to Excel?

Thanks

Re: Dataset too large to export to Excel

Reeza — Tue, 01 Jul 2014 23:44:49 GMT

You have Excel 2003? It's 11 years old .

You can either separate the file into different tabs and/or workbooks.

If you have a specific variable you can use a WHERE clause in your proc export.

proc export data=have (where =(group_var in (list of valid obs))) etc...

If you need to automate it there's other ways.

16587 - Exporting SAS® data sets of more than 65,536 rows to Microsoft Excel

Or a direct link:

ftp://ftp.sas.com/techsup/download/base/datasplitter2excel2.sas

Re: Dataset too large to export to Excel

Kurt_Bremser — Wed, 02 Jul 2014 06:14:36 GMT

The question is, what do you want to do in Excel with that kind of data?

Excel is a spreadsheet calculator and not a BI package, so you should use it for what it was designed for.

Hint: A "sheet" is usually something the size of Legal or A4.

The fact that MS tries to frankenstein Excel into some kind of database bastard by repeatedly increasing the maximum number of rows does not magically turn Excel into Oracle.

Re: Dataset too large to export to Excel

RW9 — Wed, 02 Jul 2014 08:26:11 GMT

Hi,

Reeza, I think that solution you linked to uses DDE, personally I would rather use newer tech than that. Tagset solution below.

I completely agree with KurtBremser on this, Excel really shouldn't be used for any kind of database/data transfer or pretty much anything else, you wouldn't for instance, use paint to create your outputs why use another tool not designed for it. If it is for data transfer, then delimited, XML, dataset, are all far better. For review, then consider what is actually need in the review, maybe a graph or a tabulation would be beter, maybe a profile?

data have (drop=i);
do i=1 to 100000;
a="Hello"; output;
end;
run;

proc sql;
select (NOBS/65000) + 1
into    :LOOP_COUNT
from    SASHELP.VTABLE
where   LIBNAME="WORK"
    and   MEMNAME="HAVE";
quit;

data _null_;
call execute('ods tagsets.excelxp file="s:\temp\rob\test.xls" style=statistical;');
do i=1 to &LOOP_COUNT.;
    call execute('ods tagsets.excelxp options(sheet_name="Sheet'||strip(put(i,best.))||'");
                  data tmp;
                    set have;
                    if _n_ >= '||strip(put(i-1,best.))||' * 65000 and _n_ < '||strip(put(i,best.))||' * 65000 then output;
                  run;
                  proc report data=tmp nowd;
                    column a;
                    define a / "My Var";
                  run;');
end;
call execute('ods _all_ close;');
run;

Re: Dataset too large to export to Excel

Reeza — Wed, 02 Jul 2014 22:57:43 GMT

It doesn't use DDE as far as I can see, what makes you think it does?

Tagsets are generally a good option but have a few drawbacks themselves:

1. Not a native Excel files, depending on what version it generates warnings which may not be desirable.

2. Generates big files

3. Slower than a standard proc export.

Re: Dataset too large to export to Excel

RW9 — Thu, 03 Jul 2014 09:58:41 GMT

Hi,

Well on the first link, it states:

Note: You must run the SAS 8 program interactively, or at least with a controlling terminal, because it launches Excel during its process.

This indicates that it fires up the DDE system as the only reason to open Excel would be to send DDE commands to it.

Re: Dataset too large to export to Excel

RW9 — Thu, 03 Jul 2014 10:04:31 GMT

Sorry, to add to your points:

1. Not a native Excel files, depending on what version it generates warnings which may not be desirable.

Yes, however MS has now moved to a zipped XML based format, and the proprietary binary file which used to be the native XL file will (hopefully) start to disappear.

2. Generates big files

Indeed, if size is a problem, don't use XML, use CSV instead - without quotes its probably to smallest simplest file you can do without some compression technique.

Re: Dataset too large to export to Excel

Elvin — Thu, 03 Jul 2014 15:40:45 GMT

Hi everyone, my intention to export Sasdata set to Excel is to concatenate each record and evaluate the character expression, (which I know well how to do in Excel). The bigger picture of my interest is to list out all possibilities of using the number '1', '3' ,4' '6' and operators '+', '-', '*' '/' and with "(" and ")" considered.

Here is my code and the dataset=FINAL_Last1 (where the variable 'Summing' contains 200000+ records) is where I get stuck to use Excel. How can I tell SAS to evaluate the string expression resided in variable 'Summing'? I found out I can use Macro, but would like to see your answers.

By the way, thank you Reeza for the tagset solution. That is really new to me.

The code may not be efficient to do what it intends to, I am learning and playing around with SAS. Welcome any inputs.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

OPTIONS OBS=MAX ;

OPTIONS FIRSTOBS=1 ;

data New_X1 ;

INPUT X1 $1;

DATALINES;

;

RUN;

data New_X2 ;

INPUT X2 $1;

DATALINES;

;

RUN;

data New_X3 ;

INPUT X3 $1;

DATALINES;

;

RUN;

PROC SQL;

CREATE TABLE hope AS

SELECT *

FROM New_X1, New_X2 ,New_X3;

RUN;

data New_Y (KEEP=y1s y2s y3s y4s);

array y[4] $1 ('1' '3' '4' '6');

n2=fact(4);

DO i=1 to n2;

permut_OrderImportant2=allperm(i, of y

);

put permut_OrderImportant2 +2 y1-y4;

y1s=y[1]; y2s=y[2]; y3s=y[3]; y4s=y[4];output;

END;

RUN;

data New_Z (KEEP=Z1s Z2s Z3s Z4s Z5s Z6s);

array z[6] $1 ('(' ')' '' '' '' '');

n3=fact(6);

DO i=1 to n3;

permut_OrderImportant2=allperm(i, of z

);

put permut_OrderImportant2 +2 z1-z4;

Z1s=z[1]; Z2s=z[2]; Z3s=z[3]; Z4s=z[4]; Z5s=z[5]; Z6s=z[6];output;

END;

RUN;

PROC SQL;

CREATE TABLE FINAL AS

SELECT Z1s,y1s,X1,Z2s,y2s,Z3s,X2,Z4s,y3s,Z5s,X3,y4s,Z6s

FROM hope, New_Y, New_Z;

RUN;

PROC PRINT DATA=FINAL;

RUN;

DATA FINAL_Last;

SET FINAL;

IF (z1s eq ")") THEN DELETE;

IF (z2s eq ")") THEN DELETE;

IF (z3s eq "(") THEN DELETE;

IF (z4s eq ")") THEN DELETE;

IF (z5s eq "(") THEN DELETE;

IF (z6s eq "(") THEN DELETE;

IF (z1s eq "" and z2s=")" ) THEN DELETE;

IF (z1s eq "" and z3s=")" ) THEN DELETE;

IF (z4s eq "(" and z5s=")" ) THEN DELETE;

RUN;

DATA FINAL_Last1 (KEEP=Summing);

SET FINAL_Last;

Summing= Z1s||y1s||X1||Z2s||y2s||Z3s||X2||Z4s||y3s||Z5s||X3||y4s||Z6s;

RUN;

PROC CONTENTS DATA=FINAL_Last1;

RUN;

Re: Dataset too large to export to Excel

Reeza — Thu, 03 Jul 2014 15:44:37 GMT

Why do you need to calculate all the formulas, why not calculate the values directly?

Re: Dataset too large to export to Excel

Elvin — Thu, 03 Jul 2014 15:46:41 GMT

Correction: The tagsolution is given by RW9.

Re: Dataset too large to export to Excel

Elvin — Thu, 03 Jul 2014 15:50:12 GMT

That is to find out all possibilities of using the number '1', '3' ,4' '6' and operators '+', '-', '*' '/' and with "(" and ")" considered.

Like:

1+3*6+4

1/3*6+4

1/(3*6)+4

1/3*(6+4)

1/3*6-4

1/3*(6-4)

Re: Dataset too large to export to Excel

RW9 — Thu, 03 Jul 2014 18:27:40 GMT

Well, not running your code I am suspecting your final dataset looks like this:

Summing

1+3*6+4

1/3*6+4

1/(3*6)+4

1/3*(6+4)

An you send that to Excel and it resolves the formula?

If so then you can also do:

data _null_;

set final end=last;

if _n_=1 then call execute('data wanted_result; attrib formula format=$20. result=best.;');

call execute(' formula="'||strip(summing)||'"; result='||strip(summing)||'; output;');

if last then call execute('run;');

run;

What the above will do is generate the code for a new dataset, and for each row of your final dataset produce a code line which populates formula and result in this new dataset.

Re: Dataset too large to export to Excel

rcwright — Thu, 03 Jul 2014 18:36:51 GMT

I swear I don't why people resort to excel when they have access to SAS .

Aside from the ugly code, the answer is within a couple of lines of modifications:

FILENAME f TEMP; * allocate a temporary file ;

DATA FINAL_Last1 (KEEP=Summing);

SET FINAL_Last;

FILE f; * write to temporary file ;

Summing= Z1s||y1s||X1||Z2s||y2s||Z3s||X2||Z4s||y3s||Z5s||X3||y4s||Z6s;

PUT 'eval=' summing $QUOTE. ';' summing= '; OUTPUT;'; * write this out ;

RUN;

PROC FSLIST FILEREF=f; RUN; * this is so you can look at the code ;

DATA results; * data set with the answer ;

%include f; * this includes the code we're going to write ;

RUN;