Range of using Proc Import xlsx format

[Jcorti]

Hi All,

It ia a big mess import Excel files to SAS data sets.

When I am selecting a range, I am selecting the range that contains 26 rows and 8 columns shown here  --> $A1:G26

options validvarname=any;
%let var1=M&i._ACCT_BALANCE_AM;

proc import 			out  		= bal_&var1
    					datafile 	= '/TEST/DVR/DVR_V1_PROD_01182017.xlsx'
    					dbms 		= xlsx replace;
    					sheet		= "&var1";	
					range		="&var1.$A1:G26";	
    					getnames 	= yes;

 

But the log is telling me other thing:

It is reading 9 variables

NOTE: The import data set has 25 observations and 9 variables.
NOTE: WORK.BAL_M00_ACCT_BALANCE_AM data set was successfully created.

Here is another example with another variable that I have defined in the same way:

NOTE: The import data set has 52 observations and 10 variables.
NOTE: WORK.CR_M00_ACCT_CREDIT_LIMIT_AM data set was successfully created.

I have 2 questions:

1.- Why is not reading 8 columns and 26 rows  defined on my RANGE statement?

2.- How can I fix this issue?

Thanks in advance,

JC

[Kurt_Bremser]

Have you tried to add single quotes?

As in

range = "'&var1.$A1:G26'";

I took this from

https://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003103761.htm

This also suggests to omit sheet= when specifying a range.

[Jcorti]

Thanks, 

Let me try!!

[art297]

I would add an extra period in the range statement. i.e.,

range		="&var1..$A1:G26";

Art, CEO, AnalystFinder.com 

[Jcorti]

It is reading the file but not the number columns and rows:

proc import 			out  		= bal_&var1 
    					datafile 	= '/TEST/DVR_V1_PROD_01182017.xlsx'
    					dbms 		= xlsx replace;
						sheet		= "&var1";
						range		="&var1..$A1:G26";	


********************************************LOG*************************************
MPRINT(IMPORT_LOOP):   range ="M00_ACCT_BALANCE_AM.$A1:G26";
6                                                          The SAS System                          07:50 Thursday, February 23, 2017

MPRINT(IMPORT_LOOP):   getnames = yes;
MPRINT(IMPORT_LOOP):   run;

NOTE: The import data set has 25 observations and 9 variables.
NOTE: WORK.BAL_M00_ACCT_BAL_AM data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      
MPRINT(IMPORT_LOOP):   sheet = "M00_ACCT_CR_LM_AM";
SYMBOLGEN:  Macro variable VAR2 resolves to M00_ACCT_CREDIT_LIMIT_AM
MPRINT(IMPORT_LOOP):   range ="M00_ACCT_CR_LM_AM.$A1:G26";
MPRINT(IMPORT_LOOP):   getnames = yes;
MPRINT(IMPORT_LOOP):   run;

NOTE: The import data set has 52 observations and 10 variables.
NOTE: WORK.CR_M00_ACCT_CR_LM_AM data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

[Kurt_Bremser]

Try without sheet=, you already have the sheet name in your range specification.

Since the dollar sign is the separator between sheet name and range, the second period in the range specification is unnecessary and probably wrong.

[art297]

I agree with @Kurt_Bremser that my suggestion of adding the period was incorrect in this situation.

Can you use the Excel engine. It seems to work correctly with your code:

%let var1=Sheet1;
proc import 			out = bal_&var1 
    				datafile = '/TEST/DVR_V1_PROD_01182017.xlsx'
    				dbms = excel replace;
				range ="&var1.$A1:G26";
run;

Art, CEO, AnalystFinder.com

[Jcorti]

ERROR: DBMS type EXCEL not valid for import.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

This is a big mess!!

[art297 · Accepted solution]

I just tried your code on SAS UE, using XLSX as the engine and it worked perfectly. However, I did notice (in one of your previous responses) that your log doesn't match your submitted code.  Why does the log show that macro variable var2 resolved to something, but you specified &var1 ?

[Jcorti]

Now It worked properly 🙂

Thank you both!!

NOTE: The import data set has 25 observations and 7 variables.
NOTE: WORK.BAL_M00_ACCT_BAL_AM data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      


NOTE: The import data set has 25 observations and 7 variables.
NOTE: WORK.CR_M00_ACCT_CR_AM data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
      


NOTE: The import data set has 25 observations and 7 variables.
NOTE: WORK.PYM1_M00_ACT_PYM_AM data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
      


NOTE: The import data set has 25 observations and 7 variables.
NOTE: WORK.PYM2_M00_ACCT_P_AM data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds