Proc import not working with Excel file despite uding dbms=xlsx after switching computers

[GMartel]

Hi,

I have switched computers. Both have been running SAS 9.4, with windows 10 (64). I now used SAS 9.4 (64). I am not certain whether the previous version was a 32 or 64-bytes version of SAS. My proc import code was working quite well for years and has now stopped working. I have read several postings and switched dbms=excel to dbms=xlsx as suggested. I still get the errors listed below.

proc import datafile="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL edit = AK pressors (1).xlsx"
        dbms=xlsx
        out=work.phlebo replace;
        sheet='eCRF';
        scantext=yes;
        usedate=yes;
        scantime=yes;
run;

Log:

121  proc import datafile="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL
121! edit = AK pressors (1).xlsx"
122          dbms=xlsx
123          out=work.phlebo replace;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
124          sheet='eCRF';
125          scantext=yes;
             --------
             180
126          usedate=yes;
             -------
             180
127          scantime=yes;
             --------
             180
ERROR 180-322: Statement is not valid or it is used out of proper order.

128  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

I have also tried using a libname statement with pcfiles as per this blog: https://libguides.library.kent.edu/SAS/ImportData

This is the code and errors I got:

libname myexcel pcfiles path="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL edit = AK pressors (1).xlsx" 
	scantime=yes stringdates=no dbmax_text=2000;

129  libname myexcel pcfiles path="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF -
129!  FINAL edit = AK pressors (1).xlsx"
130      scantime=yes stringdates=no dbmax_text=2000;
ERROR: Unable to locate pcfserver.exe. Please install PC Files Server.
ERROR: Error in the LIBNAME statement.

Any help would be most useful! Many thanks,

Guillaume

[Reeza]

What happens if you run this, if you get errors please post the log.

proc import datafile="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL edit = AK pressors (1).xlsx"
        dbms=xlsx
        out=work.phlebo replace;
        sheet='eCRF';
    
run;

libname myFiles xlsx "C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL edit = AK pressors (1).xlsx";

proc copy in=myFiles out=work;
select eCRF;
run;quit;

---

@GMartel wrote:

Hi,

I have switched computers. Both have been running SAS 9.4, with windows 10 (64). I now used SAS 9.4 (64). I am not certain whether the previous version was a 32 or 64-bytes version of SAS. My proc import code was working quite well for years and has now stopped working. I have read several postings and switched dbms=excel to dbms=xlsx as suggested. I still get the errors listed below.

proc import datafile="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL edit = AK pressors (1).xlsx"
        dbms=xlsx
        out=work.phlebo replace;
        sheet='eCRF';
        scantext=yes;
        usedate=yes;
        scantime=yes;
run;

Log:

121  proc import datafile="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL
121! edit = AK pressors (1).xlsx"
122          dbms=xlsx
123          out=work.phlebo replace;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
124          sheet='eCRF';
125          scantext=yes;
             --------
             180
126          usedate=yes;
             -------
             180
127          scantime=yes;
             --------
             180
ERROR 180-322: Statement is not valid or it is used out of proper order.

128  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

I have also tried using a libname statement with pcfiles as per this blog: https://libguides.library.kent.edu/SAS/ImportData

This is the code and errors I got:

libname myexcel pcfiles path="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL edit = AK pressors (1).xlsx" 
	scantime=yes stringdates=no dbmax_text=2000;

129  libname myexcel pcfiles path="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF -
129!  FINAL edit = AK pressors (1).xlsx"
130      scantime=yes stringdates=no dbmax_text=2000;
ERROR: Unable to locate pcfserver.exe. Please install PC Files Server.
ERROR: Error in the LIBNAME statement.

Any help would be most useful! Many thanks,

Guillaume

---

[GMartel]

Hi! Thank you for this. I have used the code as you have proposed. The first part (proc import) seems to generate the correct file within the Work folder. The proc copy portion does not appear to work correctly, log below:

6    proc import datafile="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL
6  ! edit = AK pressors (1).xlsx"
7            dbms=xlsx
8            out=work.phlebo replace;
9            sheet='eCRF';
10
11   run;

NOTE:    Variable Name Change.  DOB_MM/YY -> DOB_MM_YY
NOTE:    Variable Name Change.  3060postincision_Time -> _3060postincision_Time
NOTE:    Variable Name Change.  3060postincision_CVP -> _3060postincision_CVP
NOTE:    Variable Name Change.  3060postincision_CI -> _3060postincision_CI
NOTE:    Variable Name Change.  3060postincision_SVV -> _3060postincision_SVV
NOTE:    Variable Name Change.  3060postincision_UO -> _3060postincision_UO
NOTE: One or more variables were converted because the data type is not supported by the V9 engine.
      For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 427 observations and 248 variables.
NOTE: WORK.PHLEBO data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.23 seconds
      cpu time            0.21 seconds


12   libname myFiles xlsx "C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL
12 ! edit = AK pressors (1).xlsx";
NOTE: Libref MYFILES was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL edit =
      AK pressors (1).xlsx

13   proc copy in=myFiles out=work;
14       select eCRF;
15   run;

ERROR: The file MYFILES.eCRF (memtype=ALL) was not found, but appears on a SELECT statement.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.03 seconds
      cpu time            0.04 seconds

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

[Reeza]

So it looks like you have both options, import via XLSX. With the new version, you do have a new way to import files which is better IMO. It doesn't have as many features, but in general does a better job of importing data.

Excel doesn't enforce types so most applications don't try to force types on it either.

[GMartel]

Sorry for being slow... but I am not clear about which option you are referring to as being better.

[Reeza]

PROC IMPORT or using LIBNAME to import your data.

[Tom]

As the log messages indicate those statements are not supported (or needed) for the XLSX engine.

proc import
  datafile="C:\Users\guill\Documents\Research\PRICE-1\Analysis\Electronic CRF - FINAL edit = AK pressors (1).xlsx"
  dbms=xlsx
  out=work.phlebo replace
;
  sheet='eCRF';
run;

[GMartel]

Super thank you, that did appear to work well.

When you say that the other statements are not needed, should I not worry about the xlsx engine converting variable types? Or perhaps that was already occurring previously?

NOTE: One or more variables were converted because the data type is not supported by the V9 engine.
      For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 427 observations and 248 variables.
NOTE: WORK.PHLEBO data set was successfully created.

Many thanks,

Guillaume

[Tom]

---

@GMartel wrote:

Super thank you, that did appear to work well.

When you say that the other statements are not needed, should I not worry about the xlsx engine converting variable types? Or perhaps that was already occurring previously?

NOTE: One or more variables were converted because the data type is not supported by the V9 engine.
      For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 427 observations and 248 variables.
NOTE: WORK.PHLEBO data set was successfully created.

Many thanks,

Guillaume

---

That note is a bogus message that has been introduced by changes to PROC IMPORT (perhaps to support DS2 or Viya?).  It is saying that SAS datasets don't support VARCHAR() data types. As if somehow that is news to anyone that is running SAS code.