topic SAS Studio - Writing to a named range in Excel in New SAS User

SAS Studio - Writing to a named range in Excel

robulon — Thu, 12 Mar 2020 12:47:24 GMT

Hi all,

I'm a bit puzzled and hoping someone can shed some light on what's causing this issue. I was recently introduced by a colleague at work to the idea of writing data to an Excel sheet using a named range (already existing on the Excel book, but deleted using proc datasets then written again in a data step). I have done this successfully in EG as follows: -

%let xlpath=########/Arrears Level Charts.xlsx;

libname xl Excel "&xlpath";

proc datasets lib=xl nolist;
delete arrs_level.;
quit;

data xl.arrs_level.;
set arrs_level._trans_2;
run;

libname xl clear;

I have tried to use the same concept in SAS Studio but rather than the data being written to the named range on an existing tab, it is creating a new tab in the workbook with the named range name as the tab name, and creating the named range in that sheet. The only difference in the coding is that I have had to use the xlsx engine rather than the Excel one in my libname statement. Is anyone able to suggest why this might be happening, and what I would need to do to get the data written to the existing named range?

Thanks in advance for any suggestions.

Rob

Re: SAS Studio - Writing to a named range in Excel

Reeza — Thu, 12 Mar 2020 15:18:58 GMT

The engines work differently. I've found PCFILES and Excel work, but not the newer one sadly.

Fortunately ODS EXCEL is coming along.

Re: SAS Studio - Writing to a named range in Excel

robulon — Fri, 13 Mar 2020 08:13:29 GMT

Thanks Reeza,

It's no biggie as I've already got something in place that does what I need to do (I'm just proc exporting to a different tab, then using formulas to pull the data in to where I was hoping I could write the named range to).

I was just interested as to whether I was missing something fundamental.

Cheers,

Rob