Hello all, I am trying to access data in SAS from the Excel workbook at the following location: https://www.bls.gov/lpc/special_requests/us_total_hrs_emp.xlsx The workbook has multiple sheets, and ideally I'd like to be able to reference them individually. For starters I'm trying to pull cells A9:P298 from the Employment sheet and arrange the data according to the existing Excel columns. Here is my first attempt: libname Lib1 xlsx "https://www.bls.gov/lpc/special_requests/us_total_hrs_emp.xlsx"; data work.Table1; infile Lib1."Employment"; input freq $ Total_US Tot_Gen_Govt Tot_Gen_FedGovt Tot_Gen_StateGovt Armed_Forces Private_Households Non_Profits Farm Nonfarm_Business Private_Nonfarm_Business Tot_Gov_Enterprises Fed_Gov_Enterprises State_Local_Enterprises Other_NonFarm_UPFW Other_NonFarm_Proprietors; run; Here is the log for this attempt: 1 libname Lib1 xlsx "https://www.bls.gov/lpc/special_requests/us_total_hrs_emp.xlsx"; NOTE: Libref LIB1 was successfully assigned as follows: Engine: XLSX Physical Name: https://www.bls.gov/lpc/special_requests/us_total_hrs_emp.xlsx 2 3 data work.Table1; 4 infile Lib1."Employment"; ----- 22 201 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string. ERROR 201-322: The option is not recognized and will be ignored. 5 input freq $ Total_US Tot_Gen_Govt Tot_Gen_FedGovt Tot_Gen_StateGovt Armed_Forces 5 ! Private_Households Non_Profits Farm Nonfarm_Business Private_Nonfarm_Business 5 ! Tot_Gov_Enterprises Fed_Gov_Enterprises State_Local_Enterprises Other_NonFarm_UPFW 5 ! Other_NonFarm_Proprietors; 6 run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.TABLE1 may be incomplete. When this step was stopped there were 0 observations and 16 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.03 seconds Here is my second attempt: proc import datafile = "Y:\SAS\Load\us_total_hrs_emp.xlsx" dbms = xlsx out = work.Table2 replace; sheet = "Employment"; range = "EmploymentA9:P298"; getnames = no; run; Because proc import does not work with web locations, I saved the file locally and refer to that location here. I would prefer to call directly from the web and not have to do this, but I can if need be. Here is the log for this attempt: 7 proc import 8 datafile = "Y:\SAS\Load\us_total_hrs_emp.xlsx" 9 dbms = xlsx 10 out = work.Table2 11 replace; 12 sheet = "Employment"; 13 range = "EmploymentA9:P298"; 14 getnames = no; 15 run; ERROR: An exception has been encountered. Please contact technical support and provide them with the following traceback information: The SAS task name is [IMPORT] ERROR: Read Access Violation IMPORT Exception occurred at (0DD5A7B3) Task Traceback Address Frame (DBGHELP API Version 4.0 rev 5) 000000000DD5A7B3 000000000DD3D6D0 sasimxlx:tkvercn1+0x19773 000000000DD56419 000000000DD3E210 sasimxlx:tkvercn1+0x153D9 000000000DD469D1 000000000DD3E260 sasimxlx:tkvercn1+0x5991 000000000DD418DD 000000000DD3E290 sasimxlx:tkvercn1+0x89D 000000000D1D31D5 000000000DD3E298 sasimctr:tkvercn1+0x2195 000000000D1D1844 000000000DD3F640 sasimctr:tkvercn1+0x804 000000000D1B1352 000000000DD3F648 sasimpor:tkvercn1+0x312 00000000034DA066 000000000DD3FBE8 sashost:Main+0x11BA6 00000000034E011D 000000000DD3FF50 sashost:Main+0x17C5D 00007FF942054034 000000000DD3FF58 KERNEL32:BaseThreadInitThunk+0x14 00007FF9424B3691 000000000DD3FF88 ntdll:RtlUserThreadStart+0x21 NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.07 seconds cpu time 0.07 seconds Here is my third attempt: filename Table3 temp; proc http url = "https://www.bls.gov/lpc/special_requests/us_total_hrs_emp.xlsx" out = Table3; run; data work.Table4; infile Table3; input freq $ Total_US Tot_Gen_Govt Tot_Gen_FedGovt Tot_Gen_StateGovt Armed_Forces Private_Households Non_Profits Farm Nonfarm_Business Private_Nonfarm_Business Tot_Gov_Enterprises Fed_Gov_Enterprises State_Local_Enterprises Other_NonFarm_UPFW Other_NonFarm_Proprietors; run; Here is the log for this attempt: 16 filename Table3 temp; 17 18 proc http 19 url = "https://www.bls.gov/lpc/special_requests/us_total_hrs_emp.xlsx" 20 out = Table3; 21 run; NOTE: PROCEDURE HTTP used (Total process time): real time 0.20 seconds cpu time 0.04 seconds NOTE: 200 OK 22 23 data work.Table4; 24 infile Table3; 25 input freq $ Total_US Tot_Gen_Govt Tot_Gen_FedGovt Tot_Gen_StateGovt Armed_Forces 25 ! Private_Households Non_Profits Farm Nonfarm_Business Private_Nonfarm_Business 25 ! Tot_Gov_Enterprises Fed_Gov_Enterprises State_Local_Enterprises Other_NonFarm_UPFW 25 ! Other_NonFarm_Proprietors; 26 run; NOTE: The infile TABLE3 is: Filename=C:\Users\palmer_d\AppData\Local\Temp\SAS Temporary Files\_TD2096_243224LT-OPT_\#LN00056, RECFM=V,LRECL=32767,File Size (bytes)=173139, Last Modified=28Aug2019:10:58:12, Create Time=28Aug2019:10:58:12 NOTE: Invalid data for Total_US in line 1 51-630. NOTE: LOST CARD. NOTE: Invalid data errors for file TABLE3 occurred outside the printed range. NOTE: Increase available buffer lines with the INFILE n= option. freq=PK Total_US=. Tot_Gen_Govt=. Tot_Gen_FedGovt=. Tot_Gen_StateGovt=. Armed_Forces=. Private_Households=. Non_Profits=. Farm=. Nonfarm_Business=. Private_Nonfarm_Business=. Tot_Gov_Enterprises=. Fed_Gov_Enterprises=. State_Local_Enterprises=. Other_NonFarm_UPFW=. Other_NonFarm_Proprietors=. _ERROR_=1 _N_=1 NOTE: 1 record was read from the infile TABLE3. The minimum record length was 630. The maximum record length was 630. NOTE: SAS went to a new line when INPUT statement reached past the end of a line. NOTE: The data set WORK.TABLE4 has 0 observations and 16 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.04 seconds After all this is run, the Lib1 library is empty and only Table1 and Table4 are generated in SAS. Both have 0 observations. I'm using SAS 9.4 via Windowing Environment on Windows 10. If possible, please let me know how to successfully get the data from the Excel sheet to a SAS table. Much appreciated!
... View more
