topic Re: Importing excel file into sas in SAS Studio

Importing excel file into sas

Becket — Sat, 11 Apr 2020 14:37:34 GMT

Hey, I am new to SAS university edition and I am trying to import data from an excel file into SAS. Here is my code below.

LIBNAME Present 'C:\Users\asant\Documents\datamanagement\Module1.xlsx';
data Present.Module1;
set Present.Module1 SHEET='SHEET1$'n end=;
keep Student_id, Week_no_1, Week_no_2, Week_no_3, Week_no_4, Week_no_5, Week_no_6, Week_no_7, Week_no_8, Week_no_9, Week_no_10, Week_no_11, Week_no_12;
label Student_id = 'Student ID'
Week_no_1 = 'Week no 1'
Week_no_2 = 'Week no 2'
Week_no_3 = 'Week no 3'
Week_no_4 = 'Week no 4'
Week_no_5 = 'Week no 5'
Week_no_6 = 'Week no 6'
Week_no_7 = 'Week no 7'
Week_no_8 = 'Week no 8'
Week_no_9 = 'Week no 9'
Week_no_10 = 'Week no 10'
Week_no_11 = 'Week no 11'
Week_no_12 = 'Week no 12';
run;
proc contents data=Module1;
run;
proc print data=Module1;
title Math attendance;
run;
libname Present clear ;

These are the errors that I'm getting. This is my log

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 LIBNAME Present 'C:\Users\asant\Documents\datamanagement\Module1.xlsx';

NOTE: Library PRESENT does not exist.

74 data Present.Module1;

75 set Present.Module1 SHEET='SHEET1$'n end=;

ERROR 22-322: Syntax error, expecting one of the following: CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, POINT.

ERROR 76-322: Syntax error, statement will be ignored.

76 keep Student_id, Week_no_1, Week_no_2, Week_no_3, Week_no_4, Week_no_5, Week_no_6, Week_no_7, Week_no_8, Week_no_9,

76 ! Week_no_10, Week_no_11, Week_no_12;

ERROR 22-322: Syntax error, expecting one of the following: a name, -, :, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 76-322: Syntax error, statement will be ignored.

77 label Student_id = 'Student ID'

78 Week_no_1 = 'Week no 1'

79 Week_no_2 = 'Week no 2'

80 Week_no_3 = 'Week no 3'

81 Week_no_4 = 'Week no 4'

82 Week_no_5 = 'Week no 5'

83 Week_no_6 = 'Week no 6'

84 Week_no_7 = 'Week no 7'

85 Week_no_8 = 'Week no 8'

86 Week_no_9 = 'Week no 9'

87 Week_no_10 = 'Week no 10'

88 Week_no_11 = 'Week no 11'

89 Week_no_12 = 'Week no 12';

90 run;

ERROR: Library PRESENT does not exist.

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

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

91 proc contents data=Module1;

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

92 run;

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

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

93 proc print data=Module1;

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

94 title Math attendance;

95 run;

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

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

96 libname Present clear ;

NOTE: Libref PRESENT has been deassigned.

98 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: Importing excel file into sas

Kurt_Bremser — Sat, 11 Apr 2020 15:00:23 GMT

The virtual machine in which University Edition runs can not access your desktop's drive as a whole, only the shared folder you defined when you set up the instance in Virtualbix or VMPlayer. If you followed the instructions, that shared folder is accessed within the UNIX operating system of the VM as

/folders/myfolders

Store your Excel file in the Windows location of the shared folder, and use the above path in your libname statement.

sheet=

is not a valid option in a SET statement; it is also not needed. In a library defined for an Excel file, the sheets appear as datasets.

Re: Importing excel file into sas

Becket — Sat, 11 Apr 2020 15:21:12 GMT

Hi, thank you for your reply. I have done what you said. However, I am still getting these errors.

ERROR 22-322: Syntax error, expecting one of the following: CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, POINT.

ERROR 76-322: Syntax error, statement will be ignored.

76 keep Student_id, Week_no_1, Week_no_2, Week_no_3, Week_no_4, Week_no_5, Week_no_6, Week_no_7, Week_no_8, Week_no_9,

76 ! Week_no_10, Week_no_11, Week_no_12;

ERROR 22-322: Syntax error, expecting one of the following: a name, -, :, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 76-322: Syntax error, statement will be ignored.

77 label Student_id = 'Student ID'

78 Week_no_1 = 'Week no 1'

79 Week_no_2 = 'Week no 2'

80 Week_no_3 = 'Week no 3'

81 Week_no_4 = 'Week no 4'

82 Week_no_5 = 'Week no 5'

83 Week_no_6 = 'Week no 6'

84 Week_no_7 = 'Week no 7'

85 Week_no_8 = 'Week no 8'

86 Week_no_9 = 'Week no 9'

87 Week_no_10 = 'Week no 10'

88 Week_no_11 = 'Week no 11'

89 Week_no_12 = 'Week no 12';

90 run;

ERROR: Library PRESENT does not exist.

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

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

91 proc contents data=Module1;

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

92 run;

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

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

93 proc print data=Module1;

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

94 title Math attendance;

95 run;

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

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

96 libname Present clear ;

NOTE: Libref PRESENT has been deassigned.

98 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

110

User: sasdemo

Re: Importing excel file into sas

ed_sas_member — Sat, 11 Apr 2020 15:34:20 GMT

Hi @Becket

You need to specify the XLSX engine in your LIBNAME statement:

LIBNAME Present XLSX '<your path>\Module1.xlsx';

Best,

Re: Importing excel file into sas

Becket — Sat, 11 Apr 2020 15:41:37 GMT

Hi, I have done that and that's gotten rid of one error, but the other errors still persist.

Thanks

Re: Importing excel file into sas

Kurt_Bremser — Sat, 11 Apr 2020 15:47:36 GMT

Posting just the ERROR message without the statement that caused it is useless.

From your previous post, I guess that you still have not removed the syntactically invalid SHEET option.