DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

[ Edited ]

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.


Accepted Solutions
Solution
‎12-06-2016 12:25 PM
Occasional Contributor
Posts: 10

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

Posted in reply to brittneykp

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!

View solution in original post


All Replies
Super User
Super User
Posts: 7,074

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

Posted in reply to brittneykp

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.

 

Occasional Contributor
Posts: 10

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

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.

Trusted Advisor
Posts: 1,584

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

Posted in reply to brittneykp

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.

Occasional Contributor
Posts: 10

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

Posted in reply to 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.

Super User
Posts: 11,343

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

Posted in reply to brittneykp

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.

 

 

Solution
‎12-06-2016 12:25 PM
Occasional Contributor
Posts: 10

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

Posted in reply to brittneykp

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!

Respected Advisor
Posts: 4,173

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

Posted in reply to brittneykp

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";

Super User
Posts: 11,343

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

Posted in reply to brittneykp

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 254 views
  • 0 likes
  • 5 in conversation