topic Re: macro error to import multiple excel sheets and add column variables in SAS Enterprise Guide

macro error to import multiple excel sheets and add column variables

jen123 — Wed, 11 Feb 2015 20:27:45 GMT

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?

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

Tom — Wed, 11 Feb 2015 20:52:26 GMT

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

Sheet= "&sheet"

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

jen123 — Wed, 11 Feb 2015 21:21:00 GMT

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?

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

Reeza — Wed, 11 Feb 2015 23:42:46 GMT

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;

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

jen123 — Thu, 12 Feb 2015 02:48:13 GMT

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?

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

Tom — Thu, 12 Feb 2015 03:43:52 GMT

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;