Problem with Excel (.xlsx) spreadsheet data importing on SAS 9.4

[Dahcilsam]

This question is Not Answered.(Mark as assumed answered)

  

SAM LOLU Jun 9, 2015 12:54 PM   

Hi everyone, please I need an assistance in getting through my research analysis with SAS. I have the result data on excel spread sheet, but getting it imported into SAS had been a difficult issue. Import wizard is saying "Connection failed, see log for detail", and the log detail is "ERROR: Connect: Class not registered. ERROR: Error in the LIBNAME statement." I tried using the Proc Import command,it came back with "No Observation, and when I pasted the data as "Cards", there were some errors in the result ( Although I could not say if that was as a result of some missing values though represented with full stop sign). I will appreciate it anyone can help out and give me a clue of what to do, as I`ve been stocked at this point going to four weeks.

Below is the log message produced:

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.4 (TS1M1)

      Licensed to MISSISSIPPI STATE UNIVERSITY - SFA T&R, Site 70084845.

NOTE: This session is executing on the X64_7PRO  platform.

NOTE: Updated analytical products:

      SAS/STAT 13.1

      SAS/ETS 13.1

      SAS/OR 13.1

      SAS/IML 13.1

      SAS/QC 13.1

NOTE: Additional host information:

X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

NOTE: SAS initialization used:

      real time           0.95 seconds

      cpu time            0.81 seconds

1    PROC IMPORT OUT= WORK.PPTEST2011

2    DATAFILE= "E:\SasWorkAndResults\DATAFORSASPROG2011PontotocTestAges1to4.xlsx"

3        DBMS=EXCEL REPLACE;

4        GETNAMES=YES;

5        MIXED=NO;

6        SCANTEXT=YES;

7        USEDATE=YES;

8        SCANTIME=YES;

9    RUN;

ERROR: Connect: Class not registered

ERROR: Error in the LIBNAME statement.

Connection Failed.  See log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.13 seconds

      cpu time            0.07 seconds

10   IF COMM1=99 THEN DELETE;

     --

     180

ERROR 180-322: Statement is not valid or it is used out of proper order.

11       IF COMM2=99 THEN DELETE;

         --

         180

ERROR 180-322: Statement is not valid or it is used out of proper order.

12

13       Vol2=0.21099+(0.00221*((AGE2D*AGE2D)*AGE2HT));

         ----

         180

ERROR 180-322: Statement is not valid or it is used out of proper order.

14       Vol3=0.21099+(0.00221*((AGE3D*AGE3D)*AGE3HT));

         ----

         180

ERROR 180-322: Statement is not valid or it is used out of proper order.

15       Vol4=0.21099+(0.00221*((AGE4D*AGE4D)*AGE4HT));

         ----

         180

ERROR 180-322: Statement is not valid or it is used out of proper order.

16

17

18   *PROC SORT;

19   *BY BLOCK CLONE;

20   *PROC MEANS NOPRINT;

21   *BY BLOCK TAXA CLONE;

22   *PROC UNIVARIATE;

23   *VAR AGE1HT AGE2D AGE2HT AGE3D AGE3HT AGE4D AGE4HT VOL2 VOL3 VOL4;

24   *OUT=GENOTYPE MEAN=AGE1HT AGE2D AGE2HT AGE3D AGE3HT AGE4D AGE4HT VOL2 VOL3 VOL4;

25

26   PROC GLM;

ERROR: There is not a default input data set (_LAST_ is _NULL_).

27       CLASS BLOCK CLONE;

28       MODEL AGE1HT AGE2D AGE2HT AGE3D AGE3HT AGE4D AGE4HT VOL4=BLOCK CLONE BLOCK*CLONE;

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

NOTE: The previous statement has been deleted.

29       LSMEANS BLOCK;

30       MEANS BLOCK/DUNCAN ALPHA=0.01;

ERROR: Variable BLOCK not found.

NOTE: The previous statement has been deleted.

31       LSMEANS CLONE;

ERROR: Variable CLONE not found.

NOTE: The previous statement has been deleted.

32       MEANS CLONE/DUNCAN ALPHA=0.01;

ERROR: Variable CLONE not found.

NOTE: The previous statement has been deleted.

33   RUN;

ERROR: A MODEL statement must be given.

NOTE: PROCEDURE GLM used (Total process time):

      real time           19.30 seconds

      cpu time            1.21 seconds

34   PROC GLM DATA=GENOTYPE;

ERROR: File WORK.GENOTYPE.DATA does not exist.

35       CLASS BLOCK CLONE;

36       MODEL AGE1HT AGE2D AGE2HT AGE3D AGE3HT AGE4D AGE4HT VOL4=BLOCK CLONE;

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

NOTE: The previous statement has been deleted.

37       LSMEANS BLOCK;

38       MEANS BLOCK/DUNCAN ALPHA=0.01;

ERROR: Variable BLOCK not found.

NOTE: The previous statement has been deleted.

39       LSMEANS CLONE;

ERROR: Variable CLONE not found.

NOTE: The previous statement has been deleted.

40       MEANS CLONE /DUNCAN ALPHA=0.01;

ERROR: Variable CLONE not found.

NOTE: The previous statement has been deleted.

41   RUN;

ERROR: A MODEL statement must be given.

[ballardw]

The statement:

ERROR: Error in the LIBNAME statement

.

when you don't show a Libname statement would make me believe the problem is caused by something before the code you show.

These statements should be in a datastep starting with Data <datasetname>; and likely a SET statement;

IF COMM1=99 THEN DELETE;
     --
     180

ERROR 180-322: Statement is not valid or it is used out of proper order.

If you are running in a SAS server environment then the path to the Excel file may not be visible or accessible to the server as specified.

[ChrisHemedinger]

If running on Windows, you might need to change DBMS=EXCEL to DBMS=EXCELCS.  Or...you can try DBMS=XLSX (might need to adjust some of the other PROC IMPORT options).

See more at http://blogs.sas.com/content/sasdummy/2012/05/01/64-bit-gotchas/

Chris

[Dahcilsam]

Thank you for the info and the assistance. Following the suggestion you gave, this is the obtained result.

59   PROC IMPORT DATAFILE=

59 ! "E:\SasWorkAndResults\DATAFORSASPROG2011PontotocTestAges1to4.xlsx"

60       OUT= WORK.PPTEST2011

61       DBMS=EXCELCS;

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

NOTE: The previous statement has been deleted.

62       GETNAMES=YES;

63       MIXED=NO;

         -----

         180

ERROR 180-322: Statement is not valid or it is used out of proper order.

64       SCANTEXT=YES;

65       USEDATE=YES;

66       SCANTIME=YES;

67   RUN;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

My question is; do I need to do away with MIXED=NO; command

[ChrisHemedinger · Accepted solution]

EXCELCS doesn't support GETNAMES or MIXED, per this SAS note.

41060 - The EXCELCS and PCFILES engines do not support the GETNAMES= and MIXED= options

Try removing those and see if you get what you need.

Chris

[Dahcilsam]

Thanks Chris. This time around,the error message is "Unable to open file" Below is the log message;

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.4 (TS1M1)

      Licensed to MISSISSIPPI STATE UNIVERSITY - SFA T&R, Site 70084845.

NOTE: This session is executing on the X64_7PRO  platform.

NOTE: Updated analytical products:

      SAS/STAT 13.1

      SAS/ETS 13.1

      SAS/OR 13.1

      SAS/IML 13.1

      SAS/QC 13.1

NOTE: Additional host information:

X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

NOTE: SAS initialization used:

      real time           0.92 seconds

      cpu time            0.82 seconds

1    PROC IMPORT OUT= WORK.PPTEST2011

2    DATAFILE= "E:\SasWorkAndResults\DATAFORSASPROG2011PontotocTestAges1to4.xlsx"

3        DBMS=EXCELCS;

4        SCANTEXT=YES;

5        USEDATE=YES;

6        SCANTIME=YES;

7        RUN;

ERROR: Unable to open file

       E:\SasWorkAndResults\DATAFORSASPROG2011PontotocTestAges1to4.xlsx. It does not

       exist or it is already opened exclusively by another user, or you need permission

       to view its data.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.15 seconds

      cpu time            0.12 seconds

[Dahcilsam]

Can anyone be of help to tackle this resulting issue please;

	PROC IMPORT OUT= WORK.PPTEST2011
9	DATAFILE= "E:\sasmi\DATA2011PontotocTestAges1to4.xlsx"
10	DBMS=EXCELCS;
11	SCANTEXT=YES;
12	USEDATE=YES;
13	SCANTIME=YES;
14	RUN;

ERROR: This DBMS table or view cannot be accessed by the SAS System because it contains column

	names that are not unique when a SAS normalized (uppercased) compare is performed.  See
	"Naming Conventions" in the SAS/ACCESS documentation.

ERROR: Import unsuccessful.  See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

	real time	0.42 seconds
	cpu time	0.14 seconds

[ChrisHemedinger]

Try adding:

OPTIONS VALIDVARNAME=ANY;

Before the PROC IMPORT, to see if the column names come over intact.

Otherwise, you might need to fix your Excel file to have unique names in the column headers.

Chris

[Dahcilsam]

Thanks for the tip Chris, that works successfully, Now I have the data imported. The problem at this stage is that error message keep coming up that most of my Error: Variables were not found. Please I will appreciate it anyone can help out and give me a clue on what to do. The log result is as available below:

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.4 (TS1M1)

      Licensed to MISSISSIPPI STATE UNIVERSITY - SFA T&R, Site 70084845.

NOTE: This session is executing on the X64_7PRO  platform.

NOTE: Updated analytical products:

      SAS/STAT 13.1

      SAS/ETS 13.1

      SAS/OR 13.1

      SAS/IML 13.1

      SAS/QC 13.1

NOTE: Additional host information:

X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

NOTE: SAS initialization used:

      real time           0.94 seconds

      cpu time            0.77 seconds

1    OPTIONS VALIDVARNAME=ANY;

2    PROC IMPORT OUT= WORK.PPTEST2011

3    DATAFILE= "E:\sasmi\DATA2011PontotocTestAges1to4.xlsx"

4        DBMS=EXCELCS;

5        SCANTEXT=YES;

6        USEDATE=YES;

7        SCANTIME=YES;

8    RUN;

NOTE: WORK.PPTEST2011 data set was successfully created.

NOTE: The data set WORK.PPTEST2011 has 481 observations and 24 variables.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.46 seconds

      cpu time            0.14 seconds

9

10       *IF COMM1='99' THEN DELETE;

11       *IF COMM2='99' THEN DELETE;

12

13       VOL2=0.21099+(0.00221*((AGE2D*AGE2D)*AGE2HT));

         ----

         180

ERROR 180-322: Statement is not valid or it is used out of proper order.

14       VOL3=0.21099+(0.00221*((AGE3D*AGE3D)*AGE3HT));

         ----

         180

ERROR 180-322: Statement is not valid or it is used out of proper order.

15       VOL4=0.21099+(0.00221*((AGE4D*AGE4D)*AGE4HT));

         ----

         180

ERROR 180-322: Statement is not valid or it is used out of proper order.

16

17   *PROC SORT;

18       *BY BLOCK CLONE;

19   *PROC MEANS NOPRINT;

20       *BY BLOCK TAXA CLONE;

21   *PROC UNIVARIATE;

22       *VAR AGE1HT AGE2D AGE2HT AGE3D AGE3HT AGE4D AGE4HT VOL2 VOL3 VOL4;

23       *OUT=GENOTYPE MEAN=AGE1HT AGE2D AGE2HT AGE3D AGE3HT AGE4D AGE4HT VOL2 VOL3 VOL4;

24

25   PROC GLM;

26       CLASS BLOCK CLONE;

ERROR: Variable BLOCK not found.

ERROR: Variable CLONE not found.

NOTE: The previous statement has been deleted.

27       MODEL AGE1HT AGE2D AGE2HT AGE3D AGE3HT AGE4D AGE4HT VOL4=BLOCK CLONE BLOCK*CLONE;

ERROR: Variable AGE1HT not found.

ERROR: Variable AGE2D not found.

ERROR: Variable AGE2HT not found.

ERROR: Variable AGE3D not found.

ERROR: Variable AGE3HT not found.

ERROR: Variable AGE4D not found.

ERROR: Variable AGE4HT not found.

ERROR: Variable VOL4 not found.

NOTE: The previous statement has been deleted.

28       LSMEANS BLOCK;

ERROR: Variable BLOCK not found.

NOTE: The previous statement has been deleted.

29       MEANS BLOCK/DUNCAN ALPHA=0.05;

ERROR: Variable BLOCK not found.

NOTE: The previous statement has been deleted.

30       LSMEANS CLONE;

ERROR: Variable CLONE not found.

NOTE: The previous statement has been deleted.

31       MEANS CLONE/DUNCAN ALPHA=0.05;

ERROR: Variable CLONE not found.

NOTE: The previous statement has been deleted.

32   RUN;

NOTE: PROCEDURE GLM used (Total process time):

      real time           2:19.15

      cpu time            2.23 seconds

33   PROC GLM DATA=GENOTYPE;

ERROR: File WORK.GENOTYPE.DATA does not exist.

34       CLASS BLOCK CLONE;

35       MODEL AGE1HT AGE2D AGE2HT AGE3D AGE3HT AGE4D AGE4HT VOL4=BLOCK CLONE;

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

ERROR: No data set open to look up variables.

NOTE: The previous statement has been deleted.

36       LSMEANS BLOCK;

37       MEANS BLOCK/DUNCAN ALPHA=0.05;

ERROR: Variable BLOCK not found.

NOTE: The previous statement has been deleted.

38       LSMEANS CLONE;

ERROR: Variable CLONE not found.

NOTE: The previous statement has been deleted.

39       MEANS CLONE /DUNCAN ALPHA=0.05;

ERROR: Variable CLONE not found.

NOTE: The previous statement has been deleted.

40   RUN;

ERROR: A MODEL statement must be given.