topic ERROR: During insert: [Microsoft][ODBC Excel Driver] Invalid bracketing of name '`.`'. in SAS Programming

ERROR: During insert: [Microsoft][ODBC Excel Driver] Invalid bracketing of name '`.`'.

Toni2 — Tue, 11 Oct 2022 11:54:44 GMT

i am trying to extract the below data in Excel using the proc export and i receive the below error:

ERROR: During insert: [Microsoft][ODBC Excel Driver] Invalid bracketing of name '`.`'.

the dataset below has a variable with the name "." and this is what i believe causes the issue.

However, when i try to rename it using the rename below

data temp_1; 
set vol_partenon_product;
rename .=miss;
run;

i also get an error below is the log:

27         data temp_1;
28         set vol_partenon_product;
29         rename .=miss;
                  _
                  22
                  200
ERROR 22-322: Syntax error, expecting one of the following: a name, ;.  

ERROR 200-322: The symbol is not recognized and will be ignored.

30         run;

this is the data which i am trying to export to an Excel:

month	300	.
Dec2017	102260.0000000000	0.0000000000
Mar2018	100254.0000000000	0.0000000000
Jun2018	102456.0000000000	0.0000000000
Sep2018	101129.0000000000	0.0000000000

Is there any way to rename the "." variable ?

Re: ERROR: During insert: [Microsoft][ODBC Excel Driver] Invalid bracketing of name '`.`'.

Tom — Tue, 11 Oct 2022 13:14:17 GMT

You have a variable named 300 and another variable name that is just a single period?

Why? How did you make such a thing?

Are you trying to create an Excel file (EXPORT) or read from an existing Excel file (IMPORT)?

If you are exporting then run PROC CONTENTS on the existing SAS dataset.

If you are importing then show the SAS code you used to try to import the XLSX file.

Re: ERROR: During insert: [Microsoft][ODBC Excel Driver] Invalid bracketing of name '`.`'.

Toni2 — Tue, 11 Oct 2022 13:28:29 GMT

the names of variables indicate groups. I split observations in different groups and then the groups are used as names. The "." indicates the group of missing. This has been created previously in another macro in previous steps.

I am trying to export data in Excel using the proc export function. Below is the proc contents of the data i am trying to export (apologies for the format)

Alphabetic List of Variables and Attributes # Variable Type Len Format 2 300 Num 8 30.1 3 . Num 8 30.1 1 month Num 8 MONYY.

Variable "." is numeric

Re: ERROR: During insert: [Microsoft][ODBC Excel Driver] Invalid bracketing of name '`.`'.

Tom — Tue, 11 Oct 2022 14:05:05 GMT

Show the code you ran to "export" to Excel.

It works fine for me.

options validvarname=any;
data have;
  input month $ '300'n '.'n ;
cards;
Dec2017 102260.0000000000 0.0000000000
Mar2018 100254.0000000000 0.0000000000
Jun2018 102456.0000000000 0.0000000000
Sep2018 101129.0000000000 0.0000000000
;

ods excel file='c:\downloads\testing.xlsx';
proc print data=have noobs;
run;
ods excel close;

Note: You should keep not really be using those column headers as variable names. It will be much easier if you keep the data in a more normalized form and just produce that REPORT from the actual data.

This also has the benefit of letting you use VALIDVARNAME=V7 and prevent the creation of variables with goofy names.

options validvarname=v7;
data have;
  input month $ @ ;
  do name = '300','.' ;
    input value @;
    output;
  end;
  input;
cards;
Dec2017 102260.0000000000 0.0000000000
Mar2018 100254.0000000000 0.0000000000
Jun2018 102456.0000000000 0.0000000000
Sep2018 101129.0000000000 0.0000000000
;

ods excel file='c:\downloads\testing.xlsx';
proc report data=have ;
  column month value,name;
  define month/group;
  define value / ' ';
  define name / across ' ' order=data;
run;
ods excel close;

Re: ERROR: During insert: [Microsoft][ODBC Excel Driver] Invalid bracketing of name '`.`'.

Toni2 — Tue, 11 Oct 2022 14:31:25 GMT

hi thanks again. Here is the macro with the code to export in Excel

where :

ds= dataset to extract

extr_location= the path location for the file

file_name=the name of the file

lwin=my passwort

pwin=my password


%macro export_macro(extr_location=,ds=,file_name=,sheet_name=);

data _null_;
	call symput('today',compress(put(today(),ddmmyy6.)));
run;

/*options nomprint nomlogic nosymbolgen;*/
proc export dbms=excelcs data=&ds.
       	outfile="&extr_location.\&file_name._&today..xls";
       	sheet="&sheet_name";
       	version="2002";                     
		server		= "corporate_server";
		port		= 9000;
		ServerUser = "copr\&lwin";
	    ServerPass = "&pwin";                          
RUN;

%mend;

Re: ERROR: During insert: [Microsoft][ODBC Excel Driver] Invalid bracketing of name '`.`'.

Tom — Tue, 11 Oct 2022 14:35:18 GMT

Looks like you are trying to use SAS PC FILES server to allow you to write to a file on a computer other than the one where SAS is running.

If you want to rename those variables with the goofy names you need to use name literals, like in the code in my previous post.

data want;
  set have;
  rename '.'n = missing;
run;

Re: ERROR: During insert: [Microsoft][ODBC Excel Driver] Invalid bracketing of name '`.`'.

Toni2 — Tue, 11 Oct 2022 14:43:21 GMT

thank you 🙂