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 try to access it like below, it works.
libname mylib '/hardpath/datasets';
proc sql;
create table check3 as select checkfld from mylib.dream
where checkfld = "xxxx";
quit;
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?
libname mylib '/hardpath/datasets';
proc sql;
create table check3 as select distinct checkfld from mylib.dream
where checkfld = "xxxx";
quit;
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.
Can you please show us the actual LOG instead of this copy-and-paste portions of the log, beginning with the entire PROC SQL part of the log where you have "select * from TMPLIBPT.DREAM"?
Please click on the {i} icon and paste your log into the window that appears. DO NOT SKIP THIS STEP.
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;
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
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.