Help using Base SAS procedures

PROC IMPORT....multiple sheets

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

PROC IMPORT....multiple sheets

Hi All,

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

Regards

Anand Sahu


Accepted Solutions
Solution
‎11-20-2013 03:28 PM
Super Contributor
Posts: 333

Re: PROC IMPORT....multiple sheets

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

View solution in original post


All Replies
Super Contributor
Posts: 333

Re: PROC IMPORT....multiple sheets

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

Frequent Contributor
Posts: 81

Re: PROC IMPORT....multiple sheets

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;

Solution
‎11-20-2013 03:28 PM
Super Contributor
Posts: 333

Re: PROC IMPORT....multiple sheets

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

Frequent Contributor
Posts: 124

Re: PROC IMPORT....multiple sheets

[ Edited ]

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

Super User
Super User
Posts: 6,498

Re: PROC IMPORT....multiple sheets

There are many papers published on this topic.

Google Search @sas.com proc import multiple sheets

Frequent Contributor
Posts: 81

Re: PROC IMPORT....multiple sheets

Hi Tom,

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

Regards

Anand

Super User
Super User
Posts: 6,498

Re: PROC IMPORT....multiple sheets

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

SAS Employee
Posts: 1

Re: PROC IMPORT....multiple sheets

/* 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);

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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