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,
the last part of the code has a semicolon in the code that I am running.
ods excel close;
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.
Good catch! but that is just an error when I typed the code here. It is not in my code.
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
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
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.
@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.
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
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.
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.