Desktop productivity for business analysts and programmers

macro error to import multiple excel sheets and add column variables

Reply
Frequent Contributor
Posts: 94

macro error to import multiple excel sheets and add column variables

Hello, I have to import 30+ excel workbooks into SAS for analysis.  Each workbook has the same number of tabs with the same format.  There are 12 tabs per workbook, with the first tab being INSTRUCTIONS.  I need to import tabs 2-12 of each workbook.  Here is my attempt at a macro: ************************************************* %macro pim(sheet); Proc Import Out=test Datafile='/home/risk/Consumer Heatmap 2015.xls' DBMS=xls Replace; Sheet= '&sheet'; Datarow=10; Run; %mend pim; %pim(Heat Map Channel Agnostic); %pim(ATM); ************************************************ When I run the above macro, I get this error.  Note the two // before the word Consumer.  I have triple checked and retyped the file path to eliminate an possible space but still get the same error. OTE: Writing RTF Body file: EGRTF 30        FILENAME EGSR TEMP; 31        ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 32            STYLE=HtmlBlue 33            STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/6.1/Styles/HtmlBlue.css") 34            NOGTITLE 35            NOGFOOTNOTE 36            GPATH=&sasworklocation 37            ENCODING=UTF8 38            options(rolap="on") 39        ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 40        41        GOPTIONS ACCESSIBLE; 42        %pim('Heat Map Channel Agnostic'); Requested Sheet Name not found on Excel -> /home/risk//Consumer Heatmap 2015.xls Requested Input File Is Invalid ERROR: Import unsuccessful.  See SAS Log for details. Few questions: 1.  Any idea on why SAS sees two // instead of one? 2.  Since I am importing multiple sheets, when using proc import, is it possible: a.  have the multiple sheets imported as one dataset per workbook?  For example, workbook1 will have all its sheet in dataset1, workbook2 will be in dataset2, etc? b.  to add a column to indicate the channel (i.e. Agnostic, ATM, etc)?  Or do I have to do this as another datastep?

Super User
Super User
Posts: 6,845

Re: macro error to import multiple excel sheets and add column variables

Macro variable references will not be resolved inside of single quotes.  Use the double quote character instead.

Sheet= "&sheet"

Frequent Contributor
Posts: 94

Re: macro error to import multiple excel sheets and add column variables

Hi Tom - THANK YOU!!!  The double quote worked.  I should've known better.  Any chance you know the answers to the last part of my original post?  I copy/paste it below.  Do I need to post the question as a new discussion?  Thanks!!! ************************ Few questions: 1.  Any idea on why SAS sees two // instead of one? 2.  Since I am importing multiple sheets, when using proc import, is it possible: a.  have the multiple sheets imported as one dataset per workbook?  For example, workbook1 will have all its sheet in dataset1, workbook2 will be in dataset2, etc? b.  to add a column to indicate the channel (i.e. Agnostic, ATM, etc)?  Or do I have to do this as another datastep?

Super User
Posts: 19,157

Re: macro error to import multiple excel sheets and add column variables

Its a bit hard to follow your code in the current format, but it looks like a proc import.

That means you will have to create a new column in a new data step/sql step to add the column.

You can add it at the end of the macro, assuming you pass in a sheet name. If all your OUT in proc import have the name test they will overwrite each other.

%macro pim(sheet);

*proc import code;

data test;

set test;

Sheet="&Sheet";

run;

%mend;

Frequent Contributor
Posts: 94

Re: macro error to import multiple excel sheets and add column variables

I don't know why my post looked like that.  I'm going to try to post my macro here again: ************************************************* %macro pim(sheet); Proc Import Out=test Datafile='/home/risk/Consumer Heatmap 2015.xls' DBMS=xls Replace; Sheet= "&sheet"; Datarow=10; Run; %mend pim; %pim(Heat Map Channel Agnostic); %pim(ATM); ************************************************ I am sorry I do not fully understand your response Reeza.  I am a beginner at SAS (or even coding). I understand if I use "Out=test", each import will overwrite the previous.  What do I need to do so that each import (i.e. Agnostic, ATM, etc) does not overwrite one another and that I can combine them all into on dataset?

Super User
Super User
Posts: 6,845

Re: macro error to import multiple excel sheets and add column variables

A few points.

1) Copy and paste the program in WORD or some other editor and then copy and paste into the forum.  It should preserve the formatting better.

2) Can you use the LIBNAME method to access your XLS files?  It would make it much easier to copy all of the sheets without having to type the names one by one.

3) Unless the number of sheets is really large (over 20-30) you are probably much better off as a novice to just use "wallpaper code".  That is figure out how to import one sheet.  Copy the code and change the sheet name and the output data set name.  Repeat until you have imported all of the sheets. Modern (for example any thing written after 1979) make it really easy to duplicate code lines.

4) Once you have a number of data sets you can easily combine them by using a data step.

proc import out=sheet1 datafile='.....' replace ; sheet="...."; run;

....

proc import out=sheet14 datafile='.....' replace; sheet="...."; run;

data want ;

   set sheet1-sheet14 ;

run;

Ask a Question
Discussion stats
  • 5 replies
  • 506 views
  • 0 likes
  • 3 in conversation