- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi All,
Can anyone help me to understand, how to import multiple sheets using Proc Import.
Regards
Anand Sahu
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have the Tab names as 75511,75522,75533 and 75544. These are numeric.
How can mention those.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
There are many papers published on this topic.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi Tom,
I searched everywhere but couldn't get anywhere that's why seeking help from here.
Regards
Anand
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
/* 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);