Reference Excel named range on a specific sheet?

[x2008kyr]

Hi there, I am trying to automate a report by exporting data into an excel template with multiple sheets (one for each month) and a number of different already defined named ranges. I am having trouble referencing the named range on a specific sheet when trying to output the data to it. I get the following error...

ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: 'April$APR_date' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

I assume I am getting the error because of the '$' used to specify which sheet I am referring to... SAS CODE:

*create date generated;
data date;
	v1="AS OF:";
	date=today()-1;
	format date date9.;
run;

*import template;
libname R1600f excel "Z:\Information Analysis & Distribution\Performance Management\Reporting\Renal\Phase 2 Reporting\R1600 - Dialysis Treatments\WORKING\R1600 - MONTHLY2.xlsx";

*delete named ranges;
proc datasets library=R1600f  nolist;
	delete 'April$APR_date'n;
run;

data R1600f.'April$APR_date'n;
	set date;
run;

*disconnect from template;
libname R1600f clear;

I have verified that the named range in SAS is April$APR_date which corresponds to the range name APR_date on the April worksheet.

Any help would be greatly appreciated.

[Reeza]

Start by checking what SAS is seeing, I would swithc Excel to XLSX as well. 

libname R1600f xlsx "Z:\Information Analysis & Distribution\Performance Management\Reporting\Renal\Phase 2 Reporting\R1600 - Dialysis Treatments\WORKING\R1600 - MONTHLY2.xlsx";

proc datasets lib=R1600f;
run;

This should tell you all the names in the files. 

If you absolutely cannot get this working, I recommend switching to PCFILES instead.

libname R1600f pcfiles path ="Z:\Information Analysis & Distribution\Performance Management\Reporting\Renal\Phase 2 Reporting\R1600 - Dialysis Treatments\WORKING\R1600 - MONTHLY2.xlsx";

Run the same thing as above, find the data set name, drop it and then reexport your data. I've done this using PCFILES many a time, so 99% sure that should work fine. I've had issues with XLSX 

---

@x2008kyr wrote:

Hi there, I am trying to automate a report by exporting data into an excel template with multiple sheets (one for each month) and a number of different already defined named ranges. I am having trouble referencing the named range on a specific sheet when trying to output the data to it. I get the following error...

ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: 'April$APR_date' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

I assume I am getting the error because of the '$' used to specify which sheet I am referring to... SAS CODE:

*create date generated;
data date;
	v1="AS OF:";
	date=today()-1;
	format date date9.;
run;

*import template;
libname R1600f excel "Z:\Information Analysis & Distribution\Performance Management\Reporting\Renal\Phase 2 Reporting\R1600 - Dialysis Treatments\WORKING\R1600 - MONTHLY2.xlsx";

*delete named ranges;
proc datasets library=R1600f  nolist;
	delete 'April$APR_date'n;
run;

data R1600f.'April$APR_date'n;
	set date;
run;

*disconnect from template;
libname R1600f clear;

I have verified that the named range in SAS is April$APR_date which corresponds to the range name APR_date on the April worksheet.

Any help would be greatly appreciated.

---

[x2008kyr]

Hi Reeza,

Thank you for the prompt reply.

I realized what my issue was. I didn't realize that when the named ranges were created that they someone defaulted to a scope that was worksheet specific and not the global scope "workbook". Apparently SAS will only recognize named ranges that are defined with a global scope in excel.

Jessy