topic Re: proc export to XLSX- right to left layout in SAS Programming

proc export to XLSX- right to left layout

Ronein — Thu, 12 Jun 2025 10:08:51 GMT

Hello

I want to export two data sets into XLSX file.

In real word I must use proc export (and not ODS excel) because the data sets are big .

I want that sheets layout be right to left and not left to right.

I used options bidi=yes;

but still layout is left to right.


%let path="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/CARS_CLASS.xlsx";
options bidi=yes;
proc export data=sashelp.cars
outfile=&path.
DBMS=xlsx replace;
sheet='POP';
run;
proc export data=sashelp.class
outfile=&path.
DBMS=xlsx replace;
sheet='PART1_A';
run;

Re: proc export to XLSX- right to left layout

Kathryn_SAS — Thu, 12 Jun 2025 15:36:15 GMT

Currently the BIDI option is only honored for ODS and Base reporting procedures. It is not honored for PROC EXPORT. You will have to use ODS EXCEL to get the functionality you desire.

Re: proc export to XLSX- right to left layout

Ronein — Thu, 12 Jun 2025 15:43:56 GMT

But my tables are big do cannot use ods excel

Re: proc export to XLSX- right to left layout

Tom — Thu, 12 Jun 2025 15:58:29 GMT

In which case it might be better to share them using some other file format. Perhaps CSV files?

You can uses the ZIP engine to put multiple CSV files into one ZIP file.

Example using %csv_vnext macro.

filename myzip zip 'C:\downloads\csvfiles.zip';

%csv_vnext
/*----------------------------------------------------------------------
Write SAS dataset as CSV file with headers using single data step
----------------------------------------------------------------------*/
(sashelp.class   /* Input dataset name. DSNoptions allowed */
,outfile=myzip('class.csv')   /* Output fileref or quoted filename */
,dlm=',' /* Delimiter character as literal value */
,names=1  /* Write header row? */
,label=0  /* Use LABEL instead of NAME for header row? */
,format=  /* Allow overrides of formats */
);

%csv_vnext
/*----------------------------------------------------------------------
Write SAS dataset as CSV file with headers using single data step
----------------------------------------------------------------------*/
(sashelp.cars(obs=100)   /* Input dataset name. DSNoptions allowed */
,outfile=myzip('cars.csv')   /* Output fileref or quoted filename */
,dlm=',' /* Delimiter character as literal value */
,names=1  /* Write header row? */
,label=0  /* Use LABEL instead of NAME for header row? */
,format=  /* Allow overrides of formats */
);

filename myzip ;

Re: proc export to XLSX- right to left layout

Kathryn_SAS — Thu, 12 Jun 2025 16:20:19 GMT

You can use ODS EXCEL if you consider some of the following alternatives:

To prevent memory errors, you can reduce the size of larger tables by breaking them up into smaller tables. This action enables the memory to be freed after processing each of the smaller tables. This action can be accomplished by using methods such as BY-group processing or using the OBS= and FIRSTOBS= data set options to subset the data.

You also can increase the memory for the job by using the MEMSIZE system option either at start-up or in the configuration file.

Re: proc export to XLSX- right to left layout

Ronein — Thu, 12 Jun 2025 17:40:00 GMT

But csv file must have one sheet and i want to have 12 sheets....so as far as I know for this task must have xlsx and not csv

Re: proc export to XLSX- right to left layout

Tom — Thu, 12 Jun 2025 18:21:44 GMT

@Ronein wrote:
But csv file must have one sheet and i want to have 12 sheets....so as far as I know for this task must have xlsx and not csv

Which is why I showed you how to create multiple CSV files inside of a single ZIP file. That way you still only have one file to deal with.

Re: proc export to XLSX- right to left layout

Quentin — Thu, 12 Jun 2025 18:47:16 GMT

You could try tagsets.excelxp. It might not have the same memory limitations as the EXCEL destination. It writes .xml, but Excel will open it and you can do most of the same formatting.