libref not assigned error when using distinct in a proc sql sas view

cckesler — Thu, 07 Mar 2019 20:18:57 GMT

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

Re: libref not assigned error when using distinct in a proc sql sas view

ChrisNZ — Thu, 07 Mar 2019 22:07:24 GMT

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?

Re: libref not assigned error when using distinct in a proc sql sas view

cckesler — Fri, 08 Mar 2019 14:28:56 GMT

Yes, the error is the same no matter the case of the libname.

Re: libref not assigned error when using distinct in a proc sql sas view

ChrisNZ — Sun, 10 Mar 2019 04:01:39 GMT

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?

Re: libref not assigned error when using distinct in a proc sql sas view

Tom — Sun, 10 Mar 2019 17:33:33 GMT

Is this issue reproducible? Or was this just just a one-time failure?

If you can recreate it then report it to SAS support.

topic libref not assigned error when using distinct in a proc sql sas view in SAS Programming

libref not assigned error when using distinct in a proc sql sas view

Re: libref not assigned error when using distinct in a proc sql sas view

Re: libref not assigned error when using distinct in a proc sql sas view

Re: libref not assigned error when using distinct in a proc sql sas view

Re: libref not assigned error when using distinct in a proc sql sas view