topic Re: Error Importing XLSX/proc import in SAS Procedures

Error Importing XLSX/proc import

sxm367 — Tue, 02 Apr 2019 17:00:36 GMT

Hello all,

I work in IT support and have a client who's running into issues with importing .xlsx files with his new installation of SAS 9.4 on a new machine. The new machine has no problems with "CIDS applications" and they run as the user expects. The issue is specifically with "PROC IMPORT" statements.

I have no personal experience with SAS myself but will provide whatever info I can.

The relevant details I could find however were that the old installation was a 32-bit installation of 9.3(?) and 32-bit office 2016 where as the new one is 64-bit all around.

Could this be the issue? I've attached a .docx of the exact text that get's inputted that works under 32-bit/old but not on the new.

Any help, insight, or perspective is greatly appreciated,

Re: Error Importing XLSX/proc import

andreas_lds — Wed, 03 Apr 2019 09:47:30 GMT

Man members of the community won't open office-files. Please post log-messages using the {i}-icon (in Rich Text view).

Re: Error Importing XLSX/proc import

sxm367 — Wed, 03 Apr 2019 11:04:12 GMT

My apologies, here's the code in question:

   title 'SECTION 10: IMPORTS BMT.DANIA';
16046	title 'Update the file date suffix in the DATAFILE specification';
16047	proc import OUT=BT.DA
16048	DATAFILE = "C:\BTDATA\J1DA_2019_28.xlsx"
16049	DBMS=XLSX REPLACE;
NOTE: The previous statement has been deleted. 
NOTE: The previous statement has been deleted. 
16050	RANGE="Sheet1$A1 :AS&FINISH";
16051      GETNAMES=YES;
16052      MIXED=YES;


                 180
16053        TEXTSIZE=200;
                 180
ERROR 180- 322: Statement is not valid or it is used out of proper order.

I believe the spaces and tabs are formatted correctly, so I hope this shows up as it should.

Re: Error Importing XLSX/proc import

Tom — Wed, 03 Apr 2019 13:01:04 GMT

Looks like the error was before the part of the log you posted and that error is causing the PROC statement to not be recognized. Which makes all the other statements invalid.

Re: Error Importing XLSX/proc import

sxm367 — Wed, 03 Apr 2019 13:13:48 GMT

Understood Tom, I'll see if I can get the client to send me a larger/extended export of the code he's running.

Thanks again for the help!

Re: Error Importing XLSX/proc import

SuzanneDorinski — Thu, 04 Apr 2019 20:10:09 GMT

SAS online documentation lists three statements available for importing Excel files using DBMS=XLSX. They are GETNAMES, RANGE, and SHEET.

I tried adding the MIXED and TEXTSIZE statements to some PROC IMPORT code that normally works for me, and I see the same errors.

%let in_path=H:\CenSAS stuff;

proc import out=temp_class
            datafile="&in_path\export_single_sheet_to_Excel.xlsx"
			dbms=xlsx replace;
  mixed=yes;
  textsize=200;
run;

48   %let in_path=H:\CenSAS stuff;
49
50   proc import out=temp_class
51               datafile="&in_path\export_single_sheet_to_Excel.xlsx"
52               dbms=xlsx replace;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
53     mixed=yes;
       -----
       180
54     textsize=200;
       --------
       180
ERROR 180-322: Statement is not valid or it is used out of proper order.

55   run;


NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

If I don't use the MIXED and TEXTSIZE statements, the PROC IMPORT works as expected.

%let in_path=H:\CenSAS stuff;

proc import out=temp_class
            datafile="&in_path\export_single_sheet_to_Excel.xlsx"
			dbms=xlsx replace;
run;

56   %let in_path=H:\CenSAS stuff;
57
58   proc import out=temp_class
59               datafile="&in_path\export_single_sheet_to_Excel.xlsx"
60               dbms=xlsx replace;
61   run;

NOTE: The import data set has 19 observations and 5 variables.
NOTE: WORK.TEMP_CLASS data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.10 seconds
      cpu time            0.00 seconds

Re: Error Importing XLSX/proc import

Tom — Thu, 04 Apr 2019 20:20:18 GMT

You can only use the options that the DBMS you are using supports.

Re: Error Importing XLSX/proc import

sxm367 — Fri, 05 Apr 2019 14:14:07 GMT

Hey Tom,

Thanks again for the help. The client was able to export the log and it it was about 295 pages. I've pulled this set from where I see the error as well as some of the preceding lines of code before it occurs. Apologies in advance if there's erroneous lines/data. I'm not sure what to look for as far as the earlier lines causing the PROC error.

643  /* title 'BMT.DANIA AFTER ADDING PROTOCOL DATA';
644  proc print data = bmt.dania;
645    var FNAMEB LNAMEB DOBB BMTDATEB MRNB;
646    run; */
647
648
649  /* title 'CHECK MAPPINGS INTO BMT.DANIA';
650  proc freq data = BMT.DANIA;
651   tables STEPSP*STEPSB / list missing;
652   run; */
653
654
655
656  /* title 'BMT.DANIA AFTER INSERTIONS';
657  proc contents data = BMT.DANIA;
658    run; */
659
660
661  /* title 'BMT.DANIA AFTER INSERTIONS';
662  proc sort data = BMT.DANIA;
663    by BMTDATEB FNAMEB;
664    run;
665  proc print data = BMT.DANIA;
666     var FNAMEB LNAMEB BMTDATEB DIGCATB;
667     run; */
668
669
670
671  title 'SECTION 1E: IMPORTS BMT.DISEASESTATUS';
672  proc import DATAFILE="C:\BTRK\INFORMATICS\CIDS DATA
672! MODEL\Disease_Status_Reference_and_Mapping_Table_2018_07_10.xls"
673              OUT=BMT.DISEASESTATUS
674              DBMS=XLS REPLACE;
675              RANGE="Sheet1$A1:H158";
676              GETNAMES=YES;
677              MIXED=YES;
678              TEXTSIZE=200;
679              run;

NOTE:    Variable Name Change.  Parent Disease Category Code -> Parent_Disease__Category_Code
NOTE:    Variable Name Change.  Surrogate Disease Category Code ->
      Surrogate_Disease__Category_Code
NOTE:    Variable Name Change.  Parent Disease Category Name -> Parent_Disease_Category_Name
NOTE:    Variable Name Change.  Parent Disease Subtype Code -> Parent_Disease__Subtype_Code
NOTE:    Variable Name Change.  Parent Disease Subtype Name -> Parent_Disease_Subtype_Name
NOTE:    Variable Name Change.  Disease StatusCode -> Disease_Status_Code
NOTE:    Variable Name Change.  Disease Status Name -> Disease_Status_Name
NOTE:    Variable Name Change.  Valid In Prospective Data -> Valid_In_Prospective_Data
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 157 observations and 8 variables.
NOTE: BMT.DISEASESTATUS data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


680
681
682
683  title 'INSERT SECONDARY VARIABLES INTO BMT.DISEASESTATUS';
684  DATA BMT.DISEASESTATUS (keep = DIGCATD DSSTD SUBCATD SUBTYPED);
685    SET BMT.DISEASESTATUS;
686
687    informat DIGCATD SUBCATD $15.;
688    informat DSSTD $13.;
689    informat SUBTYPED $29.;
690
691
692    DIGCATD = Surrogate_Disease__Category_Code;
693
694    DSSTD =  Disease_Status_Code;
695
696    SUBCATD = Parent_Disease__Subtype_Code;
697
698    SUBTYPED = Parent_Disease__Subtype_Code;
699
700    run;

NOTE: There were 157 observations read from the data set BMT.DISEASESTATUS.
NOTE: The data set BMT.DISEASESTATUS has 157 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


701
702  /* proc contents data = BMT.DISEASESTATUS;
703    run;
704
705  proc print data = bmt.diseasestatus;
706    run; */
707
708
709
710  /* title 'SECTION 1F: IMPORTS BMT.J10';
711     NOTE: BMT.J10 is an earlier version the medically complex
712      retrospective data file as captured by Tom Klumpp, whereas BMT.J10c, which gets
713      imported in section 4 below, contains more variables and is probably cleaner.
714  data bmt.j10;
715    set bmt.j10;
716    run;  */
717
718
719
720
721  /* title 'SECTION 1G, VERSION 1 = LOCAL HOST MODE - IMPORTS DR. KLUMPPs J10 RETRO FILE INTO
721! BMT.NEFF';
722  title 'Note: As of March 2019, the only function of J10_RETRO is to enable Section 5 to
722! perform a second check of the';
723  title 'Primary key values on transplants performed from 1995 through 2016 in the event that
723! primary checks';
724  title 'fail to detect a problem, which actually did happen on one occasion in February 2019'
724! ;
725  proc import OUT=BMT.NEFF
726              DATAFILE="C:\BMTDATA\J10_RETRO_2019_03_25.xlsx"
727              DBMS=XLSX REPLACE;
728              RANGE="Sheet1$A1:AZ1223";
729              GETNAMES=YES;
730              MIXED=YES;
731              TEXTSIZE=200;
732              run;  */
733
734
735  title1 'SECTION 1G, VERSION 2 = PARTIAL PRODUCTION MODE - IMPORTS J10 NEFF (FROM INCOMPLETE
735! CIDS D/B) INTO BMT.NEFF';
736  title1 'Update the file date suffix in the DATAFILE specification and the row count suffix
736! in the RANGE specification';
737  proc import OUT=BMT.NEFF
738              DATAFILE="C:\BMTDATA\J10_NEFF_2019_03_28.xlsx"
739              DBMS=XLSX REPLACE;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
740              RANGE="Sheet1$A1:AZ201";
741              GETNAMES=YES;
742              MIXED=YES;
                 -----
                 180
743              TEXTSIZE=200;
                 --------
                 180
ERROR 180-322: Statement is not valid or it is used out of proper order.

744              run;


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

Again, a sincere thank you for the assistance. If there's any additional info needed, let me know.

Re: Error Importing XLSX/proc import

andreas_lds — Fri, 05 Apr 2019 14:29:27 GMT

Please read the message posted by @SuzanneDorinski again: the options mixed and textsize are not available when importing xlsx files. Removing them should solve the issue.