export

[Ronein]

Hello

I have 12 data sets ,each data set has 10,000 rows and 20 columns.

I want to export it into XLSX file.

I wanted to do it via defining 12 proc reports ( and define rules to color cells) but since the data sets are "big" then this idea is not good.

My question-

In such situation , what is the recommended way to export to XLSX file?

1- proc exports

2-ODS EXCEL (with 12 proc reports)- as i saw it is not good idea here but maybe i am wrong

3-XLSX engine

4-Any other way

[Kathryn_SAS]

The ODS EXCEL destination is the only one that will allow you to color cells. For this destination, a good rule of thumb for the amount of memory needed is rows x columns x 5000. You can check your current memsize setting by running the following:

proc options option=memsize;

run;

[PaigeMiller]

ODS EXCEL has an option to put each BY group into a different worksheet.

ods excel file='myexcel.xlsx' options(sheet_interval='BYGROUP');
proc report data=xxx;
    by variablename;
...
run;

[ballardw]

What makes Proc Report a problem in this context for you?

What would the reports look like? What summary statistics or group breaks would be in effect? How many rows in the expected report(s)? (Personal note: I don't expect anyone to read or use well a 10,000 line "report", that's like reading 100+ pages of text.)

What are the rules for coloring the cells like?

If you are displaying all the observations and the cell coloring is based on the value of the variable and not that of a different variable then Proc Print might also be a candidate.

Proc export is intended to move data, no style control options. Similar with XLSX engine.

[SASKiwi]

In my experience ODS EXCEL is very resource intensive for large data volumes. I'd be tempted to forgo formatting and just use PROC EXPORT or the XLSX LIBNAME engine. as it will run a lot faster. 

[Quentin]

Try tagsets.excelxp destination.  As discussed in this paper, it handles memory differently than the EXCEL destination.

https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf