BookmarkSubscribeRSS Feed
Marina1
Calcite | Level 5

Hi Team,

 

I am running a SAS program in 9.4 version and when trying to pull the external file into the SAS environment, every time the error that shows is,

 

13 data demo;
14 infile "C:\\Users\\seshi\\Documents\\PAUL_COLLISION.XLS";
-
23
ERROR 23-2: Invalid option name =.

15 input sno name$ email$ contact$ authorizationLevel$ response$;
16 RUN;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DEMO may be incomplete. When this step was stopped there were 0
observations and 6 variables.
WARNING: Data set WORK.DEMO was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.12 seconds
cpu time 0.03 seconds


17 data demo;
18 infile EXCELFILE "C:\\Users\\seshi\\Documents\\PAUL_COLLISION.XLS";

-------------------------------------------------
23
ERROR 23-2: Invalid option name "C:\\Users\\seshi\\Documents\\PAUL_COLLISION.XLS".

19 input sno name$ email$ contact$ authorizationLevel$ response$;
20 RUN;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DEMO may be incomplete. When this step was stopped there were 0
observations and 6 variables.
WARNING: Data set WORK.DEMO was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

 

Thank you,

4 REPLIES 4
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @Marina1 

 

Please post the whole program and log.

 

I am not sure why you get the first error, but it looks like a missing semicolon somewhere. The infile statement as such should work.

 

The second error is because you are mixing up 2 different SAS statements: Filename and Infile. You can declare a fileref with a filename statement and use the fileref in an in infile statement, or use the file name direct in the infile statement, but the way you do it SAS takes EXCELFILE as a fileref and the filename as an unknown option to Infile.

 

Another thing: You cannot read an EXCEL file as a text file, because it is an XML structure. So when you get it to work, you run into other problems. Use either Proc Import or an xlsx libname.

 

Here you see the problems. First example is the error generated by infile EXCELFILE <filename>, second is when the filename works, but is actually unusable with a xlsx-file.

 

36   data test;
37       infile EXCELFILE "c:\\temp\\PAUL_COLLISION.XLSX";

      -------------------------------
      23
ERROR 23-2: Invalid option name "c:\\temp\\PAUL_COLLISION.XLSX".

38       input sno name$ email$ contact$ authorizationLevel$ response$;
39   run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete.  When this step was stopped there were 0
         observations and 6 variables.
WARNING: Data set WORK.TEST was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


40
41   data test;
42       infile "c:\\temp\\PAUL_COLLISION.XLSX";
43       input sno name$ email$ contact$ authorizationLevel$ response$;
44   run;

NOTE: The infile "c:\\temp\\PAUL_COLLISION.XLSX" is:
      Filename=c:\\temp\\PAUL_COLLISION.XLSX,
      RECFM=V,LRECL=32767,File Size (bytes)=15482,
      Last Modified=27. juni 2019 10:18:03,
      Create Time=27. juni 2019 10:18:03

NOTE: Invalid data for sno in line 1 1-49.
NOTE: LOST CARD.
NOTE: Invalid data errors for file '"c:\\temp\\PAUL_COLLISION.XLSX"' occurred outside the printed
      range.
NOTE: Increase available buffer lines with the INFILE n= option.
sno=. name=¢(    email=  contact=  authorizationLevel=  response=  _ERROR_=1 _N_=1
NOTE: 1 record was read from the infile "c:\\temp\\PAUL_COLLISION.XLSX".
      The minimum record length was 630.
      The maximum record length was 630.
NOTE: SAS went to a new line when INPUT statement reached past the end of a line.
NOTE: The data set WORK.TEST has 0 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds

Kurt_Bremser
Super User

Infile is a statement for reading text files. Excel files are not text files.

Either save your data to a text file from Excel (eg .csv), or import the Excel file with proc import.

 

Since you seem to be a real beginner, I recommend that you work through the free online Programming 1 course, where such issues are covered.

Marina1
Calcite | Level 5
Hi Bremer,

Thank you for getting back to me with the the resolution it helped me a
lot to resolve my query.

Many Thank's,
Marina Chrisolite.
ballardw
Super User

First you use

infile "C:\\Users\\seshi\\Documents\\PAUL_COLLISION.XLS";

But the attachment you use is PAUL_COLLISION.XLSX so that is likely one issue somewhere.

 

Second is the repeated use of  \\ in the file path. While that may work for some forms of network connectivity Windows is going to expect something starting with C: to be a local hard drive and every single \\ should be a simple \.

 

Post log entries into a code box opened on the forum with either the{I} or "running man" icon. The _ character as part of the error message was under the place SAS determined the error occurred. The message window has reformatted the text and moved that location. The code box will maintain the formatting. If in your log it appeared under the second \ of a pair that likely confirms part of my comment above.

 

Be careful where you copy text from. Some sources will add interesting characters. Your subject line makes me think that perhaps you copied something from a web browser location line.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 828 views
  • 0 likes
  • 4 in conversation