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.
2 REPLIES 2 Super UserStart 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.