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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 161750 views
  • 7 likes
  • 6 in conversation