BookmarkSubscribeRSS Feed
mmhxc5
Quartz | Level 8

Hi all,

I would like to use the following code to output the proc tabulate to an excel file, but it at the end, the out is displayed in HTML and an empty excel file opens.

ods excel file='D:\Softwares\data.xlsx)';
proc tabulate data=xx.data02 FORMAT=5.;
class Verification_Year Product_NUMBER;
var ID_availability;
table Verification_Year ALL, ALL ID_availability*Product_NUMBER;
run;
ods excel close

The output is with a very large number of columns and maybe larger than an excel number of rows. Couldd anyone could help me to output this in an excel file - may in several sheets. 

 

Thanks,

9 REPLIES 9
mmhxc5
Quartz | Level 8

the last part of the code has a semicolon in the code that I am running.

ods excel close;
ballardw
Super User

I would not be surprised if the ) at the end of the file name is causing issues.

 

Post the LOG, copy the log including everything from the ODS excel statement to the Ods excel close.

Paste the text into a code box opened with the </> icon on the forum.

mmhxc5
Quartz | Level 8

Good catch! but that is just an error when I typed the code here. It is not in my code.

Cynthia_sas
SAS Super FREQ

Hi:

  This code using SASHELP.PRDSALE as a test worked for me, as shown below:

Cynthia_sas_0-1592431784457.png

 

  My guess is that there's some other issue, but without more information from you (exact log message, data to test) it's just a guess as to what might be wrong.

Cynthia

mmhxc5
Quartz | Level 8

I get the following error in the log.

9    ods excel file='D:\Softwares\data.xlsx';
10   proc tabulate data=xx.data02 FORMAT=5.;
NOTE: Writing HTML Body file: sashtml1.htm
11   class Verification_Year Product_NUMBER;
12   var ID_availability;
13   table Verification_Year ALL, ALL ID_availability*Product_NUMBER;
14   run;

ERROR: There is not enough memory to perform class method OM_NEW(3) of
       "SCRIPT.TCELL".
ERROR: The SAS System stopped processing this step because of insufficient
       memory.
NOTE: There were 927313 observations read from the data set
      XX.DATA02.
NOTE: At least one W.D format was too small for the number to be printed. The
      decimal may be shifted by the "BEST" format.
NOTE: PROCEDURE TABULATE used (Total process time):
      real time           22:37.82
      cpu time            22:35.09

15   ods excel close;
NOTE: Writing EXCEL file: D:\Softwares\data.xlsx

Tom
Super User Tom
Super User

How large is large?

Is there a BY variable you can use to reduce the size of each sheet?  For example you might use VERIFICATION_YEAR as the BY variable and get a different sheet for each value.

ballardw
Super User

@mmhxc5 wrote:

Hi all,

I would like to use the following code to output the proc tabulate to an excel file, but it at the end, the out is displayed in HTML and an empty excel file opens.

ods excel file='D:\Softwares\data.xlsx)';
proc tabulate data=xx.data02 FORMAT=5.;
class Verification_Year Product_NUMBER;
var ID_availability;
table Verification_Year ALL, ALL ID_availability*Product_NUMBER;
run;
ods excel close

The output is with a very large number of columns and maybe larger than an excel number of rows. Could anyone could help me to output this in an excel file - may in several sheets. 

Thanks,


If you have a lot of Product numbers then maybe it is would be better to place the id_availability and Product_number on the row and the Years as the columns.

Maybe

ods excel file='D:\Softwares\data.xlsx)';
   proc tabulate data=xx.data02 FORMAT=5.;
   class Verification_Year Product_NUMBER;
   var ID_availability;
   table ALL Product_NUMBER *ID_availability,
           Verification_Year ALL
   ;
run;
ods excel close

 

 

 

You still should post the log.And are you sure that Id_availability should be in that order? It looks very odd to have an analysis variable summing over a class variable that way.

mmhxc5
Quartz | Level 8

Hi, Here is the error from the code.

9    ods excel file='D:\Softwares\data.xlsx';
10   proc tabulate data=xx.data02 FORMAT=5.;
NOTE: Writing HTML Body file: sashtml1.htm
11   class Verification_Year Product_NUMBER;
12   var ID_availability;
13   table Verification_Year ALL, ALL ID_availability*Product_NUMBER;
14   run;

ERROR: There is not enough memory to perform class method OM_NEW(3) of
       "SCRIPT.TCELL".
ERROR: The SAS System stopped processing this step because of insufficient
       memory.
NOTE: There were 927313 observations read from the data set
      XX.DATA02.
NOTE: At least one W.D format was too small for the number to be printed. The
      decimal may be shifted by the "BEST" format.
NOTE: PROCEDURE TABULATE used (Total process time):
      real time           22:37.82
      cpu time            22:35.09

15   ods excel close;
NOTE: Writing EXCEL file: D:\Softwares\data.xlsx
Cynthia_sas
SAS Super FREQ
Hi:
There's an older posting here: https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-EXCEL-gives-error-for-larger-data-set/td-p... with a similar error message. My suggestion is that you open a track with Tech Support and then they can investigate the older cause. Meanwhile, there are some suggestions in the track that may work for you.

PROC TABULATE does run out of memory if you have too many crossings of the CLASS variables. But it sticks in my mind that the limit was some very big number so I doubt that is the issue. Tech Support is your best bet for this issue in my opinion.
Cynthia

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1523 views
  • 0 likes
  • 4 in conversation