BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AnandSahu
Calcite | Level 5

Hi All,

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

Regards

Anand Sahu

1 ACCEPTED SOLUTION

Accepted Solutions
esjackso
Quartz | Level 8

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

9 REPLIES 9
esjackso
Quartz | Level 8

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
Calcite | Level 5

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
Quartz | Level 8

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
Quartz | Level 8

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
Calcite | Level 5
Hi,

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

How can mention those.
Tom
Super User Tom
Super User

There are many papers published on this topic.

Google Search @sas.com proc import multiple sheets

AnandSahu
Calcite | Level 5

Hi Tom,

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

Regards

Anand

Tom
Super User Tom
Super User

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
SAS Employee

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9 replies
  • 175248 views
  • 7 likes
  • 6 in conversation