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: 334

Re: PROC IMPORT....multiple sheets

Posted in reply to AnandSahu

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: 334

Re: PROC IMPORT....multiple sheets

Posted in reply to AnandSahu

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: 334

Re: PROC IMPORT....multiple sheets

Posted in reply to AnandSahu

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: 134

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

Frequent Learner
Posts: 1

Re: PROC IMPORT....multiple sheets

Hi,

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

How can mention those.
Super User
Super User
Posts: 8,090

Re: PROC IMPORT....multiple sheets

Posted in reply to AnandSahu

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: 8,090

Re: PROC IMPORT....multiple sheets

Posted in reply to AnandSahu

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

Posted in reply to AnandSahu

/* 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
  • 9 replies
  • 47974 views
  • 1 like
  • 6 in conversation