Help using Base SAS procedures

Dataset too large to export to Excel

Reply
Occasional Contributor
Posts: 9

Dataset too large to export to Excel

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

Super User
Posts: 19,822

Re: Dataset too large to export to Excel

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

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

Super User
Posts: 7,827

Re: Dataset too large to export to Excel

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 9

Re: Dataset too large to export to Excel

Posted in reply to KurtBremser

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;

    Super User
    Posts: 19,822

    Re: Dataset too large to export to Excel

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

    Occasional Contributor
    Posts: 9

    Re: Dataset too large to export to Excel

    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)

    ..

    ..

    Super User
    Super User
    Posts: 7,976

    Re: Dataset too large to export to Excel

    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.

    Occasional Contributor
    Posts: 9

    Re: Dataset too large to export to Excel

    Correction: The tagsolution is given by RW9.

    Super User
    Super User
    Posts: 7,976

    Re: Dataset too large to export to Excel

    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;

    Super User
    Posts: 19,822

    Re: Dataset too large to export to Excel

    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.

    Super User
    Super User
    Posts: 7,976

    Re: Dataset too large to export to Excel

    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.

    Super User
    Super User
    Posts: 7,976

    Re: Dataset too large to export to Excel

    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.

    Contributor
    Posts: 26

    Re: Dataset too large to export to Excel

    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;

    Ask a Question
    Discussion stats
    • 12 replies
    • 7567 views
    • 2 likes
    • 5 in conversation