BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DrakePalmer
Calcite | Level 5

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Vince_SAS
Rhodochrosite | Level 12

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

View solution in original post

3 REPLIES 3
JosvanderVelden
SAS Super FREQ

/* --------------------------------------------------------------------
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
Calcite | Level 5

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
Rhodochrosite | Level 12

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 3 replies
  • 1016 views
  • 4 likes
  • 3 in conversation