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,
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.
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
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.