An Idea Exchange for SAS software and services

by Super User
on ‎06-18-2014 10:34 AM

From EG, I mostly export to EXCEL "on the fly" from the dataset "view" rather than via an Export node.

I believe this method actually creates an HTML file not a native Excel file. The method of the export is different, and there are different reasons as to why the HTML one is useful in certain circumstances, ie wanting formatted values, labels, style.

To get named ranges you will have to export.

by Super User
on ‎06-18-2014 10:45 AM

Could one solution be generating named ranges on Excel Open?  E.g. put in Workbook open:

set rng=Range("A1").Range("D1").End(xlDown).Select

ThisWorkbook.Names.Add Name:="...", RefersTo:= rng , Visible:=True

Note the above is not tested, but something like that.  Would make you Excel workbook macro enabled though.

by Occasional Contributor dsmall2112
on ‎06-19-2014 11:51 AM

Interesting solution RW9 .... for my needs I need the ranges created automatically and this sounds like I would have to peform file operation on each file to create the named ranges (after the fact).  Thanks for the reply.

by Occasional Contributor dsmall2112
on ‎06-19-2014 11:51 AM

Reeza - I'm not sure how EG is handling the export but I am prompted with the "Microsoft Excel Workbooks *.XLSX" option so would assume it is relying on native Microsoft/Access drivers to create the .XLSX.  If in fact they're creating the XLSX via some other method, then I would think it not too difficult to embed the NAMED RANGE option (but I'm no expert).  Below is screenshot of the Export to Excel "on the fly" I was referring.

I also tested exporting "as a Step in project" which creates an "Export File" icon on the Process Flow .... that exported XLSX also does NOT have NAMED RANGE.

sas eg_export to XLSX.JPG

by Occasional Contributor dsmall2112
on ‎08-11-2014 09:13 AM

I'm really surprised no one else finds the lack of "Named Range" creation of Excel exports (from SAS EG) a significant gap.

by Super User
on ‎08-11-2014 10:56 AM

Have you tested the standard Proc Export written via code?

proc export data=sashelp.class dbms=excelcs file='C:\_localdata\temp\sampleout.xlsx';


by Occasional Contributor dsmall2112
on ‎08-11-2014 11:14 AM

Yes, PROC EXPORT creates the named range.  My issue is with the EXPORT feature whilst viewing a data set.  I find this extremely handy approach to "eye ball" the data prior to exporting.

In SAS EG, is there an example of running PROC EXPORT locally to export remote datasets to Excel? (probably just need to solve for remote library references) That's another thread for sure but since we're looking at "work around" to the drop-down Export I thought I'd ask you.

Incidentally, I tried running your code from EG (ran locally which uses EXCELCS due to 64-bit Windows OS/SAS and 32-bit Office) and export completed without any logged errors, however attempting to open the file in Excel resulted in the error message below.  This seems to be an issue with SAS PC FIles Server (9.3) which I've routinely encountered ever since migrating to 64-bit.  I have noticed that, if I rename the file from .XLSX to .XLS, it opens fine and the "CLASS" named range is created.

Excelcs to XLSX error upon opening.JPG

Idea Statuses
Top Liked Authors