A view was created with the following definition (pulled from the describe view output)
select *
from TMPLIBPT.DREAM
using libname tmplibpt "/hardpath/datasets/hideit" access = readonly filelocks = none;
When I do a select to get data the sql works fine. When I add the distinct keyword I get an error saying the libname isn't assigned, but the step is still run. Trying to figure out why. I am pretty new to working with views. Can someone help me understand this?
Here is the code:
libname mylib '/aas/hardpath/datasets';
data check1;
set mylib.dream;
where checkfld = "xxxx";
run;
proc sql;
create table check2 as select checkfld from mylib.dream
where checkfld = "xxxx";
quit;
proc sql;
create table check3 as select distinct checkfld from mylib.dream
where checkfld = "xxxx";
quit;
proc sql;
describe view mylib.dream;
quit;
Here is the log:
25 GOPTIONS ACCESSIBLE;
26 libname mylib '/aas/hardpath/datasets';
NOTE: Libref MYLIB was successfully assigned as follows:
Engine: V9
Physical Name: /aas/hardpath/datasets
27
28 /*proc sql;*/
29 /*create view mylib.dream as*/
30 /* select * from TMPLIBPT.DREAM using libname tmplibpt '/aas/hardpath/datasets/hideit' access = readonly filelocks =
30 ! none;*/
31 /*quit;*/
32
33 data check1;
34 set mylib.dream;
35 where checkfld = "xxxx";
36 run;
1 The SAS System 10:55 Wednesday, February 27, 2019
INFO: Index checkfld selected for WHERE clause optimization.
NOTE: There were 102084 observations read from the data set TMPLIBPT.DREAM.
WHERE checkfld='xxxx';
NOTE: There were 102084 observations read from the data set MYLIB.DREAM.
WHERE checkfld='xxxx';
NOTE: The data set WORK.CHECK1 has 102084 observations and 11 variables.
NOTE: Compressing data set WORK.CHECK1 increased size by 12.87 percent.
Compressed is 114 pages; un-compressed would require 101 pages.
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
user cpu time 0.10 seconds
system cpu time 0.01 seconds
memory 6003.15k
OS Memory 28328.00k
Timestamp 02/27/2019 11:55:08 AM
Step Count 15 Switch Count 33
Page Faults 0
Page Reclaims 227
Page Swaps 0
2 The SAS System 10:55 Wednesday, February 27, 2019
Voluntary Context Switches 98
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 14648
37
38 proc sql;
39 create table check2 as select checkfld from mylib.dream
40 where checkfld = "xxxx";
NOTE: Compression was disabled for data set WORK.CHECK2 because compression overhead would increase the size of the data set.
1 The SAS System 10:55 Wednesday, February 27, 2019
INFO: Index checkfld selected for WHERE clause optimization.
NOTE: Table WORK.CHECK2 created, with 102084 rows and 1 columns.
41 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
user cpu time 0.03 seconds
system cpu time 0.00 seconds
memory 5681.03k
OS Memory 28584.00k
Timestamp 02/27/2019 11:55:08 AM
Step Count 16 Switch Count 3
Page Faults 0
Page Reclaims 49
Page Swaps 0
Voluntary Context Switches 18
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 1336
42
43 proc sql;
44 create table check3 as select distinct checkfld from mylib.dream
45 where checkfld = "xxxx";
ERROR: Libref TMPLIBPT is not assigned.
INFO: Index checkfld of SQL table TMPLIBPT.DREAM selected for SQL SELECT DISTINCT/UNIQUE optimization.
NOTE: Compression was disabled for data set WORK.CHECK3 because compression overhead would increase the size of the data set.
NOTE: Table WORK.CHECK3 created, with 1 rows and 1 columns.
46 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
user cpu time 0.01 seconds
system cpu time 0.01 seconds
memory 5681.03k
OS Memory 28584.00k
Timestamp 02/27/2019 11:55:08 AM
Step Count 17 Switch Count 2
Page Faults 0
Page Reclaims 26
Page Swaps 0
Voluntary Context Switches 20
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 184
3 The SAS System 10:55 Wednesday, February 27, 2019
47
48 proc sql;
49 describe view mylib.dream;
NOTE: SQL view MYLIB.DREAM is defined as:
select *
from TMPLIBPT.DREAM
using libname tmplibpt '/aas/hardpath/datasets/hideit' access = readonly filelocks = none;
50 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 406.71k
OS Memory 23460.00k
Timestamp 02/27/2019 11:55:08 AM
Step Count 18 Switch Count 0
Page Faults 0
Page Reclaims 16
Page Swaps 0
Voluntary Context Switches 3
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 48
That's an odd one.
Do you still get the error if you assign the library with an uppercase libname, since that's what was used when creating the view?
Yes, the error is the same no matter the case of the libname.
The old path seems to include a hideit folder \that's absent from the new path.
I have no clue why the libname should not be sufficient, but you could create a link to the new folder there.
Ask tech support if no one here knows, and please keep us updated?
Is this issue reproducible? Or was this just just a one-time failure?
If you can recreate it then report it to SAS support.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.