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!
This code saves the file locally, and then uses a variation of the earlier PROC IMPORT code to import the file:
options validvarname=any validmemname=extend;
filename xlsxfile 'C:\temp\us_total_hrs_emp.xlsx';
filename resphdr temp;
proc http url='https://www.bls.gov/lpc/special_requests/us_total_hrs_emp.xlsx'
out=xlsxfile
headerout=resphdr;
run; quit;
proc import
file=xlsxfile
out=work.us_total_hrs_emp
replace
dbms=xlsx;
range='Employment$a8:p298';
getnames=yes;
run; quit;
You can add a PROC DATASETS step to change the names and/or labels.
Vince DelGobbo
SAS R&D
/* --------------------------------------------------------------------
PROC IMPORT reads the data directly from the Excel source file.
The range is defined to start at row 9 because the data starts there
-------------------------------------------------------------------- */
%let path=provide_your_path_here;
PROC IMPORT
DATAFILE="&path.\us_total_hrs_emp.xlsx"
OUT=WORK.us_total_hrs_emp
REPLACE
DBMS=EXCEL;
RANGE="Employment$A9:P298";
GETNAMES=NO;
RUN;
/* --------------------------------------------------------------------
PROC DATASETS modifies the attributes of the columns within the
output data set. The label statement uses the information from rows
6, 7 and 8 from the excel file.
-------------------------------------------------------------------- */
PROC DATASETS LIBRARY=WORK NOLIST;
MODIFY us_total_hrs_emp;
FORMAT
F1 $CHAR6.
F2 F12.3
F3 F12.3
F4 F12.3
F5 F12.3
F6 F12.3
F7 F12.3
F8 F12.3
F9 F12.3
F10 F12.3
F11 F12.3
F12 F12.3
F13 F12.3
F14 F12.3
F15 F12.3
F16 F12.3 ;
INFORMAT
F1 $CHAR6.
F2 BEST12.
F3 BEST12.
F4 BEST12.
F5 BEST12.
F6 BEST12.
F7 BEST12.
F8 BEST12.
F9 BEST12.
F10 BEST12.
F11 BEST12.
F12 BEST12.
F13 BEST12.
F14 BEST12.
F15 BEST12.
F16 BEST12. ;
LABEL
F1 = "Sector - Worker coverage (millions of jobs)"
F2 = "Total U.S. Economy - all workers (millions of jobs)"
F3 = "General Government - [Total Govt. - Govt. Enterprises] - total employees (millions of jobs)"
F4 = "General Government - [Total Govt. - Govt. Enterprises] - federal employees (millions of jobs)"
F5 = 'General Government - [Total Govt. - Govt. Enterprises] - state & local employees (millions of jobs)'
F6 = "Armed Forces - personnel (millions of jobs)"
F7 = "Private Households - employees (millions of jobs)"
F8 = "Nonprofit Institutions - employees (millions of jobs)"
F9 = "Farm - all workers (millions of jobs)"
F10 = "Nonfarm Business sector - Total - all workers (millions of jobs)"
F11 = "Nonfarm Business sector - Private Nonfarm sector (excluding nonprofit institutions) - employees (millions of jobs)"
F12 = "Nonfarm Business sector - Government Enterprises - total employees (millions of jobs)"
F13 = "Nonfarm Business sector - Government Enterprises - federal employees (millions of jobs)"
F14 = 'Nonfarm Business sector - Government Enterprises - state & local employees (millions of jobs)'
F15 = "Nonfarm Business sector - Nonfarm non-employees - unpaid family workers (millions of jobs)"
F16 = "Nonfarm Business sector - Nonfarm non-employees - proprietors (millions of jobs)" ;
QUIT;
Have a look at the following paper if you have time: https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/TASS/Sukloff-ImportingExcel-F...
Thanks, this works!
Since it's only for locally saved files, is there a way to access the file through the link and save it locally using SAS?
Much appreciated
This code saves the file locally, and then uses a variation of the earlier PROC IMPORT code to import the file:
options validvarname=any validmemname=extend;
filename xlsxfile 'C:\temp\us_total_hrs_emp.xlsx';
filename resphdr temp;
proc http url='https://www.bls.gov/lpc/special_requests/us_total_hrs_emp.xlsx'
out=xlsxfile
headerout=resphdr;
run; quit;
proc import
file=xlsxfile
out=work.us_total_hrs_emp
replace
dbms=xlsx;
range='Employment$a8:p298';
getnames=yes;
run; quit;
You can add a PROC DATASETS step to change the names and/or labels.
Vince DelGobbo
SAS R&D
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.