topic Re: Range of using Proc Import xlsx format in SAS Programming

Range of using Proc Import xlsx format

Jcorti — Thu, 23 Feb 2017 15:02:04 GMT

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,

Re: Range of using Proc Import xlsx format

Kurt_Bremser — Thu, 23 Feb 2017 15:12:21 GMT

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.

Re: Range of using Proc Import xlsx format

Jcorti — Thu, 23 Feb 2017 15:16:30 GMT

Thanks,

Let me try!!

Re: Range of using Proc Import xlsx format

art297 — Thu, 23 Feb 2017 15:53:25 GMT

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

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

Art, CEO, AnalystFinder.com

Re: Range of using Proc Import xlsx format

Jcorti — Thu, 23 Feb 2017 16:16:17 GMT

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

Re: Range of using Proc Import xlsx format

Kurt_Bremser — Thu, 23 Feb 2017 16:25:55 GMT

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.

Re: Range of using Proc Import xlsx format

art297 — Thu, 23 Feb 2017 16:44:32 GMT

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

Re: Range of using Proc Import xlsx format

Jcorti — Thu, 23 Feb 2017 17:00:16 GMT

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

Re: Range of using Proc Import xlsx format

art297 — Thu, 23 Feb 2017 17:15:51 GMT

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 ?

Re: Range of using Proc Import xlsx format

Jcorti — Thu, 23 Feb 2017 17:36:00 GMT

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