cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
SifuPineapple
‎08-14-2019
  • 4 Posts
  • 0 Likes Given
  • 1 Solutions
  • 0 Likes Received

Re: Proc Import fails to import Excel Sheet Range in SAS 9.4 (64-bit)

by Fluorite | Level 6 in SAS Programming
‎08-14-2019 04:49 PM
‎08-14-2019 04:49 PM
Tom, thank your for your input.    It appears that the name variable in the proc datasets output is not pointing to the range names but in fact the worksheet names in the Excel Workbook.  Since I work in an bilingual capacity, the names for each sheet are provided in English and French.  I double checked the range names and the range names in the Excel sheet match the range names in the SAS code. (I double checked by simply copying and pasting the range names in the Excel sheet directly into the SAS code.)   I also double checked the valid names issue and it seems like you have hit the issue directly on the head - it is a worksheet names length/spaces/special character issue.  Once I removed the French names (with their associated special characters), special characters and spaces from the worksheet name, SAS immediately created the data sets exactly the way I wanted them without any issues.   So now I would like to thank Reeza and Tom for helping to resolve the problem.  Your prompt responses have exceeded expectations. 🙂   Have a great week!!   SifuPineapple ... View more

Re: Proc Import fails to import Excel Sheet Range in SAS 9.4 (64-bit)

by Fluorite | Level 6 in SAS Programming
‎08-14-2019 03:11 PM
‎08-14-2019 03:11 PM
The version of SAS i am using is SAS 9.4 TS Level 1M3,   In the proc datasets command when I use "list" underlines list and a says:       ERROR: Syntatx error , expecting on of the following: ;, ALTER, DD, DDNAME, DETAILS, FORCE GENNUM,KILL, LIB, LIBRARY, MEMTYPE, MT, MTYPE, NODETAILS, NOFS, NOLIST, NOPRINT, NOWARN, PROTECT, PW, READ.       ERROR: The option or paramter is not recognized and will be ignored. When I run proc datasets with the list command, I get 2 charts output in Results Viewer:   The first chart:                                                                   Directory Libref                             DEMO Engine                           XLSX Physical Name              F:\LFS\AR_Combo.xlsx Por   The second chart:   #                        Name                                      Member type    Obs, Entries or Indexs       Vars                  Label 1   DEFINITIONS                                                DATA                                                            7 2   DOCUMENTED - DOCUMENTéS                 DATA                                                        161 3   HISTORIQUE                                                 DATA                                                          13 4   INFORMATIONS                                            DATA                                                            3 5   TO DOCUMENT - À DOCUMENTER            DATA                                                         161 6    TRANISTORY - ÉPHéMèRES                      DATA                                                         160   As for the Excel sheet itself, like I mentioned, it works perfectly with SAS 9.3 so there is no change to sheet.  None the less I am going to recreate the Excel sheet from scratch and see if does anything.   SifuPineapple   ... View more

Re: Proc Import fails to import Excel Sheet Range in SAS 9.4 (64-bit)

by Fluorite | Level 6 in SAS Programming
‎08-14-2019 09:15 AM
‎08-14-2019 09:15 AM
Hi Reeza,   Thanks for looking into this so promptly.  However, what you suggested is also not working.  Firstly, the software crashes out on the proc datasets step you mentioned because it does not recognize "list".  Once I removed the "list" the same error shows up:              Couldn't find sheet in spreadsheet       Requested input file is invalid       ERROR: File demo.ToDocument.data does not exist.       Note: The SAS System stopped processing this step because of errors.   There is one positive thing though.  The comment, "See SAS Log for details", no longer shows up.   Sifu  ... View more

Proc Import fails to import Excel Sheet Range in SAS 9.4 (64-bit)

by Fluorite | Level 6 in SAS Programming
‎08-13-2019 03:06 PM
‎08-13-2019 03:06 PM
Hi,   I am upgrading a program to update, optimize and  work in SAS 9.4 (64-bit) as in the next month or so, the office IT people are removing SAS 9.2 and 9.3 (32-bit) from my machine.  My issue is that I have a 3 proc import statements (with DBMS=XLSX) with 3 distinctly different defined range that needs to be read in. The 3 defined ranges are in 3 different worksheets within one Excel workbook.  I double checked the Excel worksheet ranges and no matter which worksheet I am on, going to a given defined range moves me to the correct worksheet that the range is pointing to.  So the ranges exist in the Excel Workbook.Given below are the 3 lines of code that define the proc import statement.   proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.aDocumenter replace; getnames=yes; range="ToDocument"; run; proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.aSupprimer replace; getnames=yes; range="Documented"; run; proc import datafile="F:\LFS\AR_combo.xlsx" dbms=XLSX out=work.dejaDocumenter replace; getnames=yes; range="Transitory"; run; These lines of code run perfectly fine when I run the program in SAS 9.3 (32-bit).  While testing the first import statement, if I remove the range command, the proc import statement imports the whole Excel sheet without any errors in SAS 9.4 (64-bit).   When I run the import statements with the ranges, I get the following error:        Couldn't find range in spreadsheet        Requested input file is Invalid        ERROR: Import unsuccessful. See SAS Log for details.        Note: The SAS system stopped processing this step because of errors.   I am confused with the "See SAS log for details" in the error statement.  I mean I got the error for this issue by looking at the SAS log.   Just to clear everything up, I am using MS Office 2013 (32-bit) and Windows 7 Pro (64-bit).   SifuPineapple     ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎08-14-2019 08:11 PM
Quick links