LIBNAME AND XLSX

[Ihsan-Mahdi]

Hi,

I'm using the code below to read an xlsx file:

LIBNAME in xlsx '\\doh\conf\HSQA\CHS\WEMSIS\RHINO\Data Linkage\Data_2020Oct02\RHINO_linkage_WEMSIS_data_2020_Q2.xlsx';
DATA Linkage.WEMSIS_20Q2_2;

Length Destination_TransferredTo_Name $100.
Destination_ZIP $5.
CauseOfInjuryDescriptionList $400.
Cause_Of_Injury_Code_List $400.
Mechanism_Of_Injury_List $400.
Narrative $15000.
Patient_Home_Postal_Code $5.
Incident_FacOrLoc_Name $400.
Incident_Full_Address $400.
Incident_Street_Address $400.
Incident_Street_Address2 $400.
Incident_Postal_Code $5.
Incident_Location_Type $400.
US_National_Grid_Coordinates $400.
Incident_Census_Tract $100.
PriImp_CodeAndDescription $400.
SecImp_CodeAndDescription_List $400.
;
SET in.data;

RUN;
LIBNAME in clear;

Yet I keep getting the error "ERROR: Libref IN is not assigned.". Any idea how to resolve this?

Thanks!

[SASKiwi]

Is the file path you are trying to use accessible from where your SAS session is running? Are you using a remote SAS server or not?

[Ihsan-Mahdi]

The path is accessible as I used it with a proc import and the file was imported with no issues. I'm using SAS installed on my PC and not through a remote server. The SAS version is 9.4.

[Kurt_Bremser]

How do you use SAS? Locally installed on your Windows desktop, or remotely on a server, with Enterprise Guide or SAS Studio?

[Ihsan-Mahdi]

SAS studio installed on my PC.

[Kurt_Bremser]

Do you actually mean that you use University Edition?

[Ihsan-Mahdi]

I'm sorry, I think I answered wrong the first time. My SAS is a licensed version installed on my work computer. It is not the university edition.

[Kurt_Bremser]

I asked because SAS Studio is a web app that needs a web and web application server, therefore it is usually available in BI Server environments.

Which path do you get when you right-click on Files(Home) in the navigation pane and select Properties?

[Ihsan-Mahdi]

This is my SAS home screen. Not sure where to find(File) or (Home) so I could right click and find the path!

[Kurt_Bremser]

This is not SAS Studio, this is Display Manager, the standard interface of a standalone SAS installation.

So you do have local SAS, and anything available in Windows Explorer should also be available to SAS.

Please run the PROC IMPORT that worked, and the code from your initial post that failed, in one submit, and post the log from all that into a window opened with the </> button.

[Ihsan-Mahdi]

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.4 (TS1M1)

Licensed to WA DEPARTMENT OF ENTERPRISE SERVICES, Site 70025339.

NOTE: This session is executing on the W32_8PRO platform.

　

　

NOTE: Updated analytical products:

SAS/STAT 13.1

SAS/ETS 13.1

SAS/IML 13.1

NOTE: Additional host information:

W32_8PRO WIN 6.2.9200 Workstation

NOTE: SAS initialization used:

real time 54.98 seconds

cpu time 2.01 seconds

1 ODS HTML CLOSE;

2 ODS HTML;

NOTE: Writing HTML Body file: sashtml.htm

3 OPTIONS SOURCE2 NOFMTERR FMTSEARCH=(PMP.formats) COMPRESS=yes;

4 OPTIONS linesize = 100 pagesize = 50 CENTER errors=3;

5

6 LIBNAME Linkage "Z:\Linkage";

NOTE: Libref LINKAGE was successfully assigned as follows:

Engine: V9

Physical Name: Z:\Linkage

7

8 *Importing WEMSIS data;

9 proc import datafile='\\doh\conf\HSQA\CHS\WEMSIS\RHINO\Data

9 ! Linkage\Data_2020Oct02\RHINO_linkage_WEMSIS_data_2020_Q2.xlsx'

10 out=Linkage.WEMSIS_20Q2

11 DBMS=XLSX

12 REPLACE

13 ;

14 Run;

WARNING: Some character data was lost during transcoding in column: Narrative at obs 3945.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 7890.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 11550.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 12208.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 26316.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 28270.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 29155.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 29947.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 30089.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 30116.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 30333.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 30407.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 57527.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 57965.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 62666.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 62768.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 69678.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 69698.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 69712.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 70437.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 70475.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 77891.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 80381.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 94336.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 96349.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 102575.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 106853.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 107034.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 107045.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 108116.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 109838.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 111927.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 111936.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 112043.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 112169.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 112584.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 112849.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 115155.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 121130.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 122620.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 127130.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 132192.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 134544.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 134835.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 135147.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 135327.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 137526.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 156744.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 157373.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 157391.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 158406.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 158446.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 162556.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 166333.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 166648.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 167370.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 176748.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 188562.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 191092.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 217047.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 217209.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 221328.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 224189.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 226362.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 240211.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 244532.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 244587.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 248049.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 252652.

WARNING: Some character data was lost during transcoding in column: Narrative at obs 255680.

NOTE: The import data set has 257135 observations and 33 variables.

NOTE: Compressing data set LINKAGE.WEMSIS_20Q2 decreased size by 90.24 percent.

Compressed is 1321 pages; un-compressed would require 13534 pages.

NOTE: LINKAGE.WEMSIS_20Q2 data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 36:13.50

cpu time 1:48.28

　

15

16

17 *Importing using xlsx engine;

18

19 LIBNAME in xlsx '\\doh\conf\HSQA\CHS\WEMSIS\RHINO\Data

19 ! Linkage\Data_2020Oct02\RHINO_linkage_WEMSIS_data_2020_Q2.xlsx';

ERROR: The XLSX engine cannot be found.

ERROR: Error in the LIBNAME statement.

20 DATA Linkage.WEMSIS_20Q2_2;

21

22 Length Destination_TransferredTo_Name $100.

23 Destination_ZIP $5.

24 CauseOfInjuryDescriptionList $400.

25 Cause_Of_Injury_Code_List $400.

26 Mechanism_Of_Injury_List $400.

27 Narrative $15000.

28 Patient_Home_Postal_Code $5.

29 Incident_FacOrLoc_Name $400.

30 Incident_Full_Address $400.

31 Incident_Street_Address $400.

32 Incident_Street_Address2 $400.

33 Incident_Postal_Code $5.

34 Incident_Location_Type $400.

35 US_National_Grid_Coordinates $400.

36 Incident_Census_Tract $100.

37 PriImp_CodeAndDescription $400.

38 SecImp_CodeAndDescription_List $400.

39 ;

40 SET in.data;

ERROR: Libref IN is not assigned.

41

42 RUN;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set LINKAGE.WEMSIS_20Q2_2 may be incomplete. When this step was stopped there

were 0 observations and 17 variables.

WARNING: Data set LINKAGE.WEMSIS_20Q2_2 was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 9.73 seconds

cpu time 0.04 seconds

　

43 LIBNAME in clear;

WARNING: Libref IN is not assigned.

[Kurt_Bremser]

This

ERROR: The XLSX engine cannot be found.

is the crucial piece of information.

Note for future debugging: always start at the top of the log. The first problem is the most important, the last the least important. 

Normally, this ERROR means that you do not have ACCESS to PC Files licensed or installed. But since you could use DBMS=XLSX in PROC IMPORT, this is not the case, so I suspect some deeper problem with your installation. Please open a track with SAS Technical Support, as I do not think we might be able to fix it from here.

[Ihsan-Mahdi]

Will do. Thank you so much for your kind assistance!

[Kurt_Bremser]

It would be great if you could keep us informed about the outcome. Your problem seems to be without precedence here, so the solution will be an interesting addition to our "knowledge base".

[SASKiwi]

@Ihsan-Mahdi  - What happens if you try the EXCEL engine in your LIBNAME?

LIBNAME in EXCEL '\\doh\conf\HSQA\CHS\WEMSIS\RHINO\Data Linkage\Data_2020Oct02\RHINO_linkage_WEMSIS_data_2020_Q2.xlsx';