proc export to XLSX- right to left layout

[Ronein]

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;
 

[Kathryn_SAS]

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.

[Ronein]

But my tables are big do cannot use ods excel

[Tom]

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 ;

[Ronein]

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

[Tom]

---

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

[Kathryn_SAS]

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.

[Quentin]

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.