topic Re: PROC IMPORT....multiple sheets in SAS Procedures

PROC IMPORT....multiple sheets

AnandSahu — Wed, 20 Nov 2013 05:35:06 GMT

Hi All,

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

Regards

Anand Sahu

Re: PROC IMPORT....multiple sheets

esjackso — Wed, 20 Nov 2013 13:51:56 GMT

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.

Re: PROC IMPORT....multiple sheets

Tom — Wed, 20 Nov 2013 14:30:24 GMT

There are many papers published on this topic.

Google Search @sas.com proc import multiple sheets

Re: PROC IMPORT....multiple sheets

AnandSahu — Wed, 20 Nov 2013 18:03:07 GMT

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;

Re: PROC IMPORT....multiple sheets

AnandSahu — Wed, 20 Nov 2013 18:11:06 GMT

Hi Tom,

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

Regards

Anand

Re: PROC IMPORT....multiple sheets

Tom — Wed, 20 Nov 2013 18:30:14 GMT

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

Re: PROC IMPORT....multiple sheets

esjackso — Wed, 20 Nov 2013 20:28:14 GMT

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

Re: PROC IMPORT....multiple sheets

mathias — Mon, 23 May 2016 13:22:11 GMT

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

Re: PROC IMPORT....multiple sheets

RoshanJha — Wed, 14 Jun 2017 11:47:00 GMT

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

Re: PROC IMPORT....multiple sheets

PONNAGANTI — Sun, 27 Aug 2017 17:12:47 GMT

Hi,

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

How can mention those.