How do I import Excel data from a webpage?

[DrakePalmer]

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! 

[JosvanderVelden]

/* --------------------------------------------------------------------
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... 

[DrakePalmer]

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

[Vince_SAS · Accepted solution]

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