Exporting sas dataset to excel named ranges

Question

I have few datasets in SAS. They are to be exported to a excel file in some locations. Each dataset to be exported to some range(named ranges are defined in that excel). Is there any possibility to export datasets into excel for a specific "named ranges" which are predefined in existing excel file.

Thanks, Ravi

What have you tried so far? There are many resources for this which you can find with some very basic googling. — user667489
@user667489 I don't think this is a duplicate because it asks about anmed ranges. — Reeza
@ravi teja pandilla As phrased the answer to your question, is yes this can be done, as to how, please demonstrate what you've tried and we can help from there. — Reeza
@Reeza:to be frank i have not tried it anything yet with SAS. as workaround first i exported data into another excel file and then by using VBA i copied to specified named range. Please suggest if we can bypass VBA and export directly to specific named range directly from SAS. Thanks, — ravi teja pandilla

Stu Sztukowski Stu Sztukowski · Accepted Answer · 2015-09-16T16:23:33

There is a solution to export directly to an Excel named range provided you have SAS/ACCESS to Excel. For this example, assume that you have Office installed in your SAS environment, the Excel file is stored in C:\Data, and you have a named range called Named_Range.

First, you want to make a connection to the Excel data set using the libname engine:

libname xls Excel 'C:\Data\Excel_Data.xlsx';

You can then output directly to the named range like a data set. If there are contents already in it, be sure to delete them first using PROC DATASETS.

proc datasets lib=xls nolist;
    delete Named_Range;
quit;

data xls.Named_Range;
    set have;
run;

This is just one example of using it, but the thing that's so great about the libname engine is how it takes external data sets and lets you treat them like SAS data sets. If your named range had the right dimensions, you could theoretically output directly to it using any procedure that can produce a data set.

If you do not have SAS/ACCESS to Excel, I am unsure of a solution specifically with named ranges, though you could work around it using the RANGE= option in PROC EXPORT with the xls/xlsx dbms type.

Sources

Exporting sas dataset to excel named ranges

1 Answers