topic Re: Problem with Excel (.xlsx) spreadsheet data importing on SAS 9.4 in Statistical Procedures

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

Dahcilsam — Tue, 09 Jun 2015 17:01:09 GMT

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: 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.

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.

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;

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.

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.

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.

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

ballardw — Tue, 09 Jun 2015 17:02:03 GMT

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.

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

ChrisHemedinger — Tue, 09 Jun 2015 17:06:16 GMT

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

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

Dahcilsam — Tue, 09 Jun 2015 17:16:27 GMT

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.

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

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

ChrisHemedinger — Tue, 09 Jun 2015 17:35:38 GMT

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

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

Dahcilsam — Tue, 09 Jun 2015 17:44:35 GMT

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

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

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

Dahcilsam — Tue, 09 Jun 2015 20:01:08 GMT

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

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

ChrisHemedinger — Tue, 09 Jun 2015 20:37:40 GMT

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

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

Dahcilsam — Wed, 10 Jun 2015 18:22:28 GMT

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

10 *IF COMM1='99' THEN DELETE;

11 *IF COMM2='99' THEN DELETE;

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.

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;

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.

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.