BookmarkSubscribeRSS Feed
Elvin
Calcite | Level 5

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

12 REPLIES 12
Reeza
Super User

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

Kurt_Bremser
Super User

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.

Elvin
Calcite | Level 5

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;

    Reeza
    Super User

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

    Elvin
    Calcite | Level 5

    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)

    ..

    ..

    RW9
    Diamond | Level 26 RW9
    Diamond | Level 26

    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.

    Elvin
    Calcite | Level 5

    Correction: The tagsolution is given by RW9.

    RW9
    Diamond | Level 26 RW9
    Diamond | Level 26

    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;

    Reeza
    Super User

    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.

    RW9
    Diamond | Level 26 RW9
    Diamond | Level 26

    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.

    RW9
    Diamond | Level 26 RW9
    Diamond | Level 26

    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.

    rcwright
    Calcite | Level 5

    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;

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 12 replies
    • 23113 views
    • 2 likes
    • 5 in conversation