topic Re: Proc tabulate does not export to excel - blank excel in New SAS User

Proc tabulate does not export to excel - blank excel

mmhxc5 — Wed, 17 Jun 2020 21:31:51 GMT

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,

Re: Proc tabulate does not export to excel - blank excel

mmhxc5 — Wed, 17 Jun 2020 21:45:58 GMT

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

ods excel close;

Re: Proc tabulate does not export to excel - blank excel

ballardw — Wed, 17 Jun 2020 21:46:47 GMT

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.

Re: Proc tabulate does not export to excel - blank excel

mmhxc5 — Wed, 17 Jun 2020 22:09:02 GMT

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

Re: Proc tabulate does not export to excel - blank excel

Cynthia_sas — Wed, 17 Jun 2020 22:11:20 GMT

Hi:

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

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

Re: Proc tabulate does not export to excel - blank excel

Tom — Wed, 17 Jun 2020 22:18:16 GMT

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.

Re: Proc tabulate does not export to excel - blank excel

ballardw — Wed, 17 Jun 2020 22:46:07 GMT

@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.

Re: Proc tabulate does not export to excel - blank excel

mmhxc5 — Wed, 17 Jun 2020 23:25:36 GMT

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

Re: Proc tabulate does not export to excel - blank excel

mmhxc5 — Thu, 18 Jun 2020 17:35:46 GMT

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

Re: Proc tabulate does not export to excel - blank excel

Cynthia_sas — Fri, 19 Jun 2020 00:42:31 GMT

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/192648 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