PROC IMPORT....multiple sheets

[AnandSahu]

Hi All,

Can anyone help me to understand, how to import multiple sheets using Proc Import.

Regards

Anand Sahu

[esjackso]

Probably need more information than what was posted.

In general you would have a separate proc import step for each sheet that you want to import.

EJ

[AnandSahu]

I'm using the following codes to import three sheets from a single workbook so can i get a program that allow me to get the same result with a single code.

The Excel file is attached.

proc import out= Customer_details

    datafile = 'E:\SAS DOC\project\Credit Banking.xls'

    dbms = Excel;

    sheet = 'Customer Acqusition';

    getnames = yes;

    run;

proc import out= Expanse

    datafile = 'E:\SAS DOC\project\Credit Banking.xls'

    dbms = Excel;

    sheet = 'Spend';

    getnames = yes;

    run;

proc import out= payment

    datafile = 'E:\SAS DOC\project\Credit Banking.xls'

    dbms = Excel;

    sheet = 'Repayment';

    getnames = yes;

run;

[esjackso · Accepted solution]

There isnt code to read in multiple sheets from one proc import statement that I know about.

The best you could do would be to macrotize the invocation of the proc step... something like:

%macro pim(sheet);

proc import out= payment

    datafile = 'E:\SAS DOC\project\Credit Banking.xls'

    dbms = Excel;

    sheet = "&sheet";

    getnames = yes;

run;

%mend piim;

%pim(Customer Acqusition);

%pim(Spend);

%pim(Repayment);

EJ

[mathias]

Hi,

I'm searching for the same answer and tried this solution (which seems to work for many others)

but it doesn't work on my system and I wonder why?

I get this error for which I can't find any solution on other forums.:

ERROR: DBMS type EXCEL not valid for import.

I'm working with SAS 9.3 in SAS EG 5.1 (5.100.0.12019) (64-bit) on Windows7

EDIT: I found out I don't have SAS/ACCESS

[PONNAGANTI]

Hi,

I have the Tab names as 75511,75522,75533 and 75544. These are numeric.

How can mention those.

[Tom]

There are many papers published on this topic.

Google Search @sas.com proc import multiple sheets

[AnandSahu]

Hi Tom,

I searched everywhere but couldn't get anywhere that's why seeking help from here.

Regards

Anand

[Tom]

Did you read any of the articles that the link I posted found by searching using Google?

How about this one from 2011?  http://www.nesug.org/Proceedings/nesug11/cc/cc17.pdf

[RoshanJha]

/* Macro for importing data in SAS which is available in multiple sheets of a single excel file */

%macro dataload(sheet);

proc import out= &sheet
datafile = 'location of excel file\filename.xlsx'
dbms = XLSX replace;
sheet = "&sheet";
getnames = yes;
run;

%mend dataload;
%dataload(Contract);
%dataload(Instrument);
%dataload(Protection);
%dataload(Counterparty);