BookmarkSubscribeRSS Feed
Deepti44
Fluorite | Level 6

Hi ,

 

I have a weekly file of > 50K records , want to export to excel via SAS ODS, but couldn't able to export, i have splitted the file >50 K with multiple datasets , but still i coudn't able to export everything in 1 sheet.Can you please help me with this.

 

Thanks

Deepti

9 REPLIES 9
PaigeMiller
Diamond | Level 26

Tell us what happens. What is the exact word-for-word error message?

 

What version of Excel? Are you using .xlsx or .xls?

 

Also, please tell us any other relevant details.

--
Paige Miller
Deepti44
Fluorite | Level 6

i'M USING .XLSX

 

WHEN I'M USING ods EXCEL DIRECTLY(WITHOUT SPLITTING ORIGINAL DATASET) , IT SAYS SAS HAS INSUFFICIENT MEMORY.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

IT HELPS IF YOU SHOUT!

 

Why do you want 50k or more records in an Excel file for?  Whilst they have extended the limit for number of rows in later versions of Excel it really isn't the best place to put this amount of data.  If it is for data transfer, use CSV or XML.  If it is for a review output, then summarise the data then output to Excel.  An Excel file of 50k records is of zero use to anyone.

PaigeMiller
Diamond | Level 26

If it says insufficient memory, then your computer does not have enough memory to complete that task taking into account any other tasks that might also be running. Your computer needs more memory. (Or as others have said, export to a different format)

 

By the way, when I ask "What is the exact word-for-word error message?" I don't really want you to take two words out of the error message, I want the COMPLETE entire whole unedited word-for-word error message.

--
Paige Miller
ballardw
Super User

How many more than 50,000 records?

How are you attempting to export the data to excel? Show the code and the log in a code box opened with the forum {I} menu icon to preserve formatting of log text.

 

Splitting the dataset and attempting to get it all into one sheet is going to add a lot of complexity and likely introduce unwanted elements such as column header information in the body of the sheet.

Reeza
Super User

@Deepti44 wrote:

Hi ,

 

I have a weekly file of > 50K records , want to export to excel via SAS ODS, but couldn't able to export, i have splitted the file >50 K with multiple datasets , but still i coudn't able to export everything in 1 sheet.Can you please help me with this.

 

Thanks

Deepti


You're doing something wrong and since we can't see your code we can't say what it is. 

 

ODS EXCEL can export up to a million records, and the 1 million row limit is an Excel limitation. 

 

 

Deepti44
Fluorite | Level 6

Reeza,

 

This is the error im getting  and below is the code i used , i have tried both xls and xlsx.

ERROR: The SAS System stopped processing this step because of insufficient memory.

 

/*Starting ODS from here*/
ods _all_ close;
ods excel file="/WBIO_Shared/RECORDS/ADM9900_Reports/Level2/340_Participations/participations_&run_date..xls"
style=sasdocprinter
options(Sheet_name='Data'
Orientation= "LANDSCAPE"
Embedded_Titles='OFF'
frozen_headers ='1'
Autofilter='YES'
Absolute_Column_Width='9,12,32,16,43,16,24,13,21,10,20,16,14,13,13,16,14,13,35,13,6,10');
options missing=' ';
PROC PRINT DATA=final_v2 noobs LABEL
style(DATA)=[Foreground=Black background=WHITE fontsize=9 pt Font_Face=Arial]
style(HEADER)=[Foreground=Black background=LIGHTGREY fontsize=11 pt Font_Face=Arial Font_weight=Bold JUST=LEFT]
style(DATA)=data[width=1000% tagattr='wrap:yes'];
Var
caseid
ACCOUNTID
CASE_NUMBER
ACCOUNT_NAME
OBLIGOR_NAME
OBLIGOR_NUMBER
OBLIGATION_NUMBER
case_reason
OTHER_REASON
PRIORITY
REQUESTOR_ORGANIZATION
REQUESTOR_NAME
case_owner
case_createdby
STATUS
REQUEST_DATE
REQUEST_TIME
effective_date_time
effective_date
EXPECTED_TURNAROUND
DATE_TIME_OPENED
date_time_closed
min_opened_date
min_closed_date
max_closed_date
RECORD_TYPE
LASTMODIFIEDDATE
REOPENED
REALTIME_OPEN_CASESTATUS
DAYS_SINCE_OPENED
CREATED_MONTH
COMPLETED_MONTH
OPEN_OR_CLOSED
INTITAL_COMPLETION_CYCLE_TIME
MET_EFFECTIVE_DATE
REOPEN_CYCLE_TIME


;
Label caseid='Case-ID'
ACCOUNTID='Account-Id'
CASE_NUMBER='Case Number'
ACCOUNT_NAME='Account Name'
OBLIGOR_NAME='Obligor Name'
OBLIGOR_NUMBER='Obligor Number'
OBLIGATION_NUMBER='Obligation Number'
case_reason='Case Reason'
OTHER_REASON='Other Reason'
PRIORITY='Priority'
REQUESTOR_ORGANIZATION='Requestor Organisation'
REQUESTOR_NAME='Requestor Name'
case_owner='Case Owner'
case_createdby='Case Createdby'
STATUS='Status'
REQUEST_DATE='Request Date'
REQUEST_TIME='Request Time'
effective_date_time='Effective DateTime'
effective_date='Effective Date'
EXPECTED_TURNAROUND='Expected TurnAround'
DATE_TIME_OPENED='DateTime Opened'
date_time_closed='DateTime Closed'
min_opened_date='Minimum Opened Date'
min_closed_date='Minimum Closed Date'
max_closed_date='Maximum Closed Date'
RECORD_TYPE='Record Type'
LASTMODIFIEDDATE='LastModified Date'
REOPENED='ReOpened'
REALTIME_OPEN_CASESTATUS='Realtime Open CaseStatus'
DAYS_SINCE_OPENED='Days Since Opened'
CREATED_MONTH='Created Month'
COMPLETED_MONTH='Completed Month'
OPEN_OR_CLOSED='Open or Closed'
INTITAL_COMPLETION_CYCLE_TIME='Initial Completion Cycle Time'
MET_EFFECTIVE_DATE='Met Effective Date'
REOPEN_CYCLE_TIME='Reopen CycleTime'

;
Run;

ODS excel Close;

 

Reeza
Super User

Can you increase your memory settings?

 

And what version of SAS do you have? ODS EXCEL should be XLSX files (it produces native XLSX files) and went to production in SAS 9.4 TS1M3, before that it was pre-production and had some bugs. 

 

See this older post that had the same issues which resolved with changing the memory settings:

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-Excel-and-Insufficient-Memory-Error-how-to...

 

Edit: This was noted as an issue in SAS 9.4 TS1M3 and fixed in SAS 9.4 TS1M4.

http://support.sas.com/kb/56/985.html

PaigeMiller
Diamond | Level 26

You are not using .xlsx as you stated earlier, you are using .xls. You need to change this. However, I don't think this would be the cause of running out of memory.

--
Paige Miller

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 4073 views
  • 0 likes
  • 5 in conversation