Range of using Proc Import xlsx format

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Range of using Proc Import xlsx format

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

 

 


Accepted Solutions
Solution
‎02-23-2017 12:37 PM
PROC Star
Posts: 7,471

Re: Range of using Proc Import xlsx format

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 ?

 

View solution in original post


All Replies
Super User
Posts: 7,771

Re: Range of using Proc Import xlsx format

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 42

Re: Range of using Proc Import xlsx format

Posted in reply to KurtBremser

Thanks, 

Let me try!!

PROC Star
Posts: 7,471

Re: Range of using Proc Import xlsx format

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

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

Art, CEO, AnalystFinder.com 

Contributor
Posts: 42

Re: Range of using Proc Import xlsx format

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
      

 

Super User
Posts: 7,771

Re: Range of using Proc Import xlsx format

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 7,471

Re: Range of using Proc Import xlsx format

I agree with @KurtBremser 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

 

Contributor
Posts: 42

Re: Range of using Proc Import xlsx format

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

Solution
‎02-23-2017 12:37 PM
PROC Star
Posts: 7,471

Re: Range of using Proc Import xlsx format

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 ?

 

Contributor
Posts: 42

Re: Range of using Proc Import xlsx format

Now It worked properly Smiley Happy

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 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 415 views
  • 0 likes
  • 3 in conversation