topic What is the problem with my code? in SAS Programming

What is the problem with my code?

DingDing — Sun, 17 Jul 2016 14:51:01 GMT

The first question is that I can import the EXCEL file. It is a very simple line, but I can find what the problem is

PROC IMPORT DATAFILE='D:\sasprogramming\Ex1.xlsx'  OUT= Ex1 DBMS=xlsx REPLACE;
RUN;

here is the log:

ERROR: Error opening XLSX file -> D:\sasprogramming\THEORY\Class_2\Home_Exercises\Ex1.xlsx .  It is either not an Excel spreadsheet 
or it is damaged.   Error code=80000808
Requested Input File Is Invalid
ERROR: Import unsuccessful.  See SAS Log for details.

The Excel file (see attachement) is complete and I can open it smoothly, so I really wonder where the problem is.

The second question: I am a new SAS EG user, and everytime I run the same line it creates a new process in the left area. It looks weird, so I am wondering is it normal? If not, what can I do for this?

Re: What is the problem with my code?

Tom — Sun, 17 Jul 2016 16:13:57 GMT

Your XLSX file looks fine. What version of SAS are you using?

154   libname x xlsx "&path\Ex1.xlsx";
NOTE: Libref X was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: C:\Downloads\Ex1.xlsx
155   proc copy inlib=x outlib=work; run;

NOTE: Copying X.SHEET1 to WORK.SHEET1 (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: The import data set has 395 observations and 100 variables.
NOTE: There were 395 observations read from the data set X.SHEET1.
NOTE: The data set WORK.SHEET1 has 395 observations and 100 variables.
NOTE: Copying X.SHEET2 to WORK.SHEET2 (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: The import data set has 0 observations and 0 variables.
NOTE: There were 0 observations read from the data set X.SHEET2.
NOTE: The data set WORK.SHEET2 has 0 observations and 0 variables.
NOTE: Copying X.SHEET3 to WORK.SHEET3 (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: The import data set has 0 observations and 0 variables.
NOTE: There were 0 observations read from the data set X.SHEET3.
NOTE: The data set WORK.SHEET3 has 0 observations and 0 variables.

Re: What is the problem with my code?

DingDing — Sun, 17 Jul 2016 19:02:45 GMT

Hi, I use SAS enterprise guide 7. Can you import the data file with the same codes?

Re: What is the problem with my code?

Reeza — Sun, 17 Jul 2016 19:11:00 GMT

Resave your file.

Open it and save as XLSX workbook. I tried to preview it and it wouldn't open in my viewer so that would indicate that there is something abnormal with the file to me.

The proc copy will import all the sheets in the workbook which may or may not be what you want.

Re: What is the problem with my code?

DingDing — Sun, 17 Jul 2016 19:38:31 GMT

Thank you very much @Reeza and @Tom, I found I make a stupid mistake that I run SAS through a server, so I shouldn't run it through a local path. Now my first question is solved, but still the second question is still confusing. Do you have any idea about that? Why I everytime I run the code, it automatically create a sas2(2), sas2(2)(2),sas2(2)(2), sas2(2)(2) and so on. Are they normal?

Re: What is the problem with my code?

Reeza — Sun, 17 Jul 2016 20:38:31 GMT

No, that's not normal. Check the options and see if you have an autosave of some kind set up. That's the only thing I could think that would create a copy each time.

Re: What is the problem with my code?

DingDing — Sun, 17 Jul 2016 21:10:36 GMT

Hi, it's from a remote server, so the setting is automatically, and I can't change any seting up