topic Re: Export sas dataset of >50000 records via ods excel in SAS Programming

Export sas dataset of >50000 records via ods excel

Deepti44 — Mon, 05 Feb 2018 15:10:15 GMT

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

Re: Export sas dataset of >50000 records via ods excel

PaigeMiller — Mon, 05 Feb 2018 15:23:54 GMT

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.

Re: Export sas dataset of >50000 records via ods excel

ballardw — Mon, 05 Feb 2018 15:25:54 GMT

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.

Re: Export sas dataset of >50000 records via ods excel

Deepti44 — Mon, 05 Feb 2018 15:26:31 GMT

i'M USING .XLSX

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

Re: Export sas dataset of >50000 records via ods excel

RW9 — Mon, 05 Feb 2018 15:30:08 GMT

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.

Re: Export sas dataset of >50000 records via ods excel

PaigeMiller — Mon, 05 Feb 2018 15:33:55 GMT

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.

Re: Export sas dataset of >50000 records via ods excel

Reeza — Mon, 05 Feb 2018 15:50:01 GMT

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

Re: Export sas dataset of >50000 records via ods excel

Deepti44 — Mon, 05 Feb 2018 16:12:37 GMT

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;

Re: Export sas dataset of >50000 records via ods excel

Reeza — Mon, 05 Feb 2018 16:24:43 GMT

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-get-around/td-p/320661

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

Re: Export sas dataset of >50000 records via ods excel

PaigeMiller — Mon, 05 Feb 2018 16:34:40 GMT

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.