Hi Team,
In the code shown below I want to reference multiple sheets and create only one table
my excel file has multiple spreadsheets..
example:
M00_ACCT_BAL_AM to M24_ACCT_BAL_AM (25 spreadsheets)
M00_ACCT_CR_AM to M24_ACCT_CR_AM (25 spreadsheets)
How Can I reference them on my macro?
Thanks and regards,
%macro import_loop;
%let outobs=max;
%do i1=0 %to 24;
%let i = %sysfunc( putn(&i1,z2.));
%let sheet="M&i._ACCT_BAL_AM"; /* Not Working */
%let sheet="M&i_ACCT_CR_AM"; /* Not Working */
proc import out = testv1_&i (rename =('Current %'n=Current_per 'Previous %'n=Previous_per '% Change'n=Change_per))
datafile = '/test/DVR_V1_PROD_01182017.xls'
dbms = xls replace ;
sheet = "&sheet"; /*I want to reference both Arrays */
/*sheet = "M&i._ACCT_BAL_AMT"; this worked but I need to reference 2 different arrays*/
getnames = yes;
run;
data testv1_&i (drop='Run Date:'n I) ;
set testv1_&i ;
run;
data allMyData (keep=SheetObject Range_Start Range_End Current_Count
Previous_Count Current_per Previous_per Change_per);
length SheetObject $32;
set testv1_:
indsname=name ;
Rename
'Range Start'n=Range_Start
'Range End'n =Range_End
'Current Count'n=Current_Count
'Previous Count'n=Previous_Count
;
SheetObject = cats("M", scan(name, 3, "._") , "_ACCT_BAL_AM");
run;
%end;
%mend import_loop;
Do you have SAS 9.4 and are the files actual .XLSX files? If so then save yourself some time and use libname excel:
libname f1 excel "<pathtofile>\M00_ACCT_BAL_AM to M24_ACCT_BAL_AM.xlsx";
Then you can do most things with each tab as:
data want; set f1.Sheet1; run;
And if you want to know what sheets are in the file:
data want; set sashelp.vtable (where=(libname="F1")); run;
Hi,
I have my excel file in "xls" format.
Is there a way that I can reference them like **Keyword parameters** ??
Example:
%macro import_loop (sheet=,);
Well, you need to tell the program what the sheet names are somehow. If the sheet names remain constant then you can do:
%macro import_sheet (sheet=); proc import...; sheet="&sheet."; run; %mend import_sheet; data _null_; length sheet $200; input sheet $; call execute(cats('%import_sheet (sheet=',sheet,');')); datalines; Sheet1 Sheet_abc ... ; run;
What would be better however is to drop the XLS format which is ancient history in terms of file formats, or alternatively drop Excel altogether. Have a small VBA script dump the contents of the XLS out to separate CSV files, then have a datastep read each one in using a fixed import program. Excel is a really bad data medium, and then you confound this by using a guessing procedure (proc import) to get data from there.
Before wrapping the code in a macro, I strongly suggest getting the code down to run one import successfully.
You won't know what names will be assigned when non-conforming SAS names are found UNTIL you run such code. Some versions of proc import delete the first character of a variable name in such cases, while others replace them with an underscore.
The following worked on my system:
proc import out = testb1_01 (rename =(Current__=Current_per Previous__=Previous_per __Change=Change_per)) datafile = '/test/DVR_V1_PROD_01182017.xls' dbms = xls replace ; sheet = "M00_ACCT_BAL_AM"; /*I want to reference both Arrays */ /*sheet = "M&i._ACCT_BAL_AMT"; this worked but I need to reference 2 different arrays*/ getnames = yes; run;
If that works for you, then you can accomodate the two sets of sheets with something like:
%let bsheet="M&i._ACCT_BAL_AM"; /* Not Working */ %let csheet="M&i_ACCT_CR_AM"; /* Not Working */ proc import out = testb1_&i. (rename =(Current__=Current_per Previous__=Previous_per __Change=Change_per)) datafile = '/test/DVR_V1_PROD_01182017.xls' dbms = xls replace ; sheet = "&bsheet."; /*I want to reference both Arrays */ /*sheet = "M&i._ACCT_BAL_AMT"; this worked but I need to reference 2 different arrays*/ getnames = yes; run; proc import out = testc1_&i. (rename =(Current__=Current_per Previous__=Previous_per __Change=Change_per)) datafile = '/test/DVR_V1_PROD_01182017.xls' dbms = xls replace ; sheet = "&csheet."; /*I want to reference both Arrays */ /*sheet = "M&i._ACCT_BAL_AMT"; this worked but I need to reference 2 different arrays*/ getnames = yes; run;
Art, CEO, AnalystFinder.com
I was planning to do different with **proc import**, Actually I have 6 arrays on my excel file.
So my assumption was weather I can do the **proc import** with 2 arrays in only one import I would be able to do it for 6 arrays
I thought this part could be referenced in only one macro variable:
%let bsheet="M&i._ACCT_BAL_AM"; /* Not Working */ %let csheet="M&i_ACCT_CR_AM"; /* Not Working */
It seems like it will be very tough make it in only **one import**, so the best solution should be generate 6 macro variables with each array??
I'm not familiar with the concept of arrays in excel. Do you mean named ranges?
I, for one, would need a better description of what your workbooks look like in order to provide any suggestion.
Attaching one of them would really help.
Art, CEO, AnalystFinder.com
Yes, I mean basically about **Ranges**
I have to import 150 spreadsheet from one excel file
This is an example of the names of the excel sheets:
M00_ACCT_BAL_AM to M24_ACCT_BAL_AM ( 25 workbooks)
M00_ACCT_CRED_LIM_AM to M24_ACCT_CRED_LIM_AM ( 25 workbooks)
..
..
..
.. so on ...
and total number is 150
Thanks
Yes, you can use libname as I mention above. However it sounds like - and this is quite common, you have a real mess of Excel. This is typical with an unstructured free for all that is Excel. In most cases when I get something like this (well after going mad at whoever provided such a thing) is to process it using VBA which is embedded in Excel. Its a very simple language. Basically loop over your ranges and dump the data to a csv, literally a couple of lines of code:
http://stackoverflow.com/questions/27234134/loop-through-all-named-ranges-in-a-excel-sheet
Add a SaveAS to the above's solution and voila. I would dump all the data out to one CSV putting the name of the range as variable 1 - assuming the data is all the same? - if its not, then how are you going to use the data further? If it is the same then one datastep to import that one CSV file, then you have one dataset with all the data and a variable which identifies which named range it came from.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.