topic Re: Proc Import Reference multiple Spreadsheets in SAS Programming

Proc Import Reference multiple Spreadsheets

Jcorti — Tue, 21 Feb 2017 14:15:10 GMT

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;

Re: Proc Import Reference multiple Spreadsheets

RW9 — Tue, 21 Feb 2017 14:22:23 GMT

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;

Re: Proc Import Reference multiple Spreadsheets

Jcorti — Tue, 21 Feb 2017 14:30:26 GMT

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

Re: Proc Import Reference multiple Spreadsheets

RW9 — Tue, 21 Feb 2017 14:44:58 GMT

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.

Re: Proc Import Reference multiple Spreadsheets

art297 — Tue, 21 Feb 2017 15:08:49 GMT

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

Re: Proc Import Reference multiple Spreadsheets

Jcorti — Tue, 21 Feb 2017 15:28:14 GMT

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??

Re: Proc Import Reference multiple Spreadsheets

art297 — Tue, 21 Feb 2017 15:59:45 GMT

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

Re: Proc Import Reference multiple Spreadsheets

Jcorti — Tue, 21 Feb 2017 16:16:37 GMT

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

Re: Proc Import Reference multiple Spreadsheets

Peter_C — Tue, 21 Feb 2017 20:52:44 GMT

With a libname statement using the excel engine, each of the range names (subject to the validvarname=v7 rules) can be used as table names.
It is as if, under the hood, an import is performed for each range. However, your datastep SET statement only lists the multiple libref.range_name.
One convenience I have used before:
PROC SQL nolist ;
select catx('.', libname, memname)
into :ranges separated by ' '
from dictionary.members
where libname = 'LIBREF'
and memname not contain '$'
;
Quit ;
* replace LIBREF with your exvel libname statement libref.;
data work.all_ranges ;
Set.&ranges ;
run ;

Further conditions in the WHERE clause could restrict the range names if needed.
If you have and need to use, range names which do not comply with the V7 rules, try the NLITERAL() function on the memname value.

Re: Proc Import Reference multiple Spreadsheets

RW9 — Wed, 22 Feb 2017 09:17:35 GMT

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.