BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brittneykp
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
brittneykp
Obsidian | Level 7

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

8 REPLIES 8
Tom
Super User Tom
Super User

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
Obsidian | Level 7

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
Garnet | Level 18

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Opal | Level 21

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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