Macro Program won't compile because imported data contains variable "DO"

[brittneykp]

I have an import problem. I am using the following code to import a datafile, which is an Excel Workbook produced by a different agency:

PROC IMPORT OUT= WORK.Q1_2015X

DATAFILE= "\\filepath\filename.xls"

DBMS=XLS REPLACE;

SHEET= "Q1 Oct-Dec14 (Items 15-29)";

DATAROW=6;

RUN;

It's imbedded in a Macro program, which is designed to import the data for many quarters at a time. Their first four rows have a bunch of staggered headers with merged cells that don't make for good variable names. Row 5 would be a good alternative for row headers, but I don't know how to tell it to use them, so I've used the code above which mostly names the variables after the Excel columns. This was working great until my Macro program wouldn't compile because it thought there were unenclosed "DO" loops - because one of the columns is "DO" and I have a rename statement DO = CELL_262.

How can I fix this problem? Is there a better way to import this data sheet so that I can get better header names (using Row 5), or is there a way to rename the automatically created variable "DO" without the Macro program compiler thinking I am starting a DO loop?

I'm running BASE SAS 9.4. I do have SAS ACCESS/PC FILES and could do some other types of import from Excel, I'm just not very familiar with the different options available.

[Tom]

Your diagnosis of the problem is wrong. There is nothing wrong with making a variable named DO or using DO=CELL_252 in a RENAME statement or RENAME= dataset option.

Perhpas look earlier in your program for a missing semi-colon or unbalanced quotes.

[brittneykp]

Hmm.  I've reviewed my code several times for syntax - I have two %DO loops in the macro program, and both loops have the bright blue coloring to designate them as macro functions, so they seem to be fully enclosed.  I did a search for "DO" just to see if I was missing one, and that's when the variable named "DO" came up.  Now I'm flummoxed.

[Shmuel]

1) Try datarow=5; getnames = yes;

2) In case it didn't help, post your code to rename column names,

    maybe you can define: rename 'DO'n = CELL_262.

[brittneykp]

I read the error code more closely, and it specifies that it's one of my %DO statements that is unenclosed, which confirms that it isn't the DO variable that is throwing things off.  I'll keep reviewing the code to see if I can find the syntax error.

[ballardw]

Since the question is about the macro program behavior it would be a good idea to post the code for the entire macro.

And welcome to the joys of why Excel is a very poor data interchange medium.

I almost invariably convert the files to CSV and read them with a data step. A problem with relying on proc import to read multiple files is that you are almost always going to get different variable properties that aren't obvious until you go to combine the datasets. Generally the lengths of character variables will change from set tot set and depending on the actual contents some variables will change from numeric to character. A data step reading CSV (or other delimited format) can set the lengths and types consistently by use of attributes and/or informat statements. Then changing the input file name and output data set name are all that is needed to read each file.

[brittneykp · Accepted solution]

I found the problem!  I did not know that the Macro Program compiler doesn't ignore commented out sections - it was reading an unenclosed %DO loop that was in a green comment section.

Thank you all for your help!

[Patrick]

Instead of addressing and importing a whole sheet you can also address a named or absolut range in a sheet.

To do so use RANGE instead of SHEET and then syntax as below (reading starts from cell B5 in example below).

RANGE= "Q1 Oct-Dec14 (Items 15-29)$B5:XX100000";

[ballardw]

It may help others if you show the offending code, the correction you made and mark that post as the solution to the question. Then someone with a similar problem will more easily find the solution.