BookmarkSubscribeRSS Feed
jen123
Fluorite | Level 6

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?

5 REPLIES 5
Tom
Super User Tom
Super User

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

Sheet= "&sheet"

jen123
Fluorite | Level 6

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?

Reeza
Super User

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;

jen123
Fluorite | Level 6

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?

Tom
Super User Tom
Super User

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;

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 1530 views
  • 0 likes
  • 3 in conversation