topic Re: 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

cckesler — Tue, 26 Feb 2019 20:13:05 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 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.

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

PaigeMiller — Tue, 26 Feb 2019 20:45:29 GMT

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.

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

cckesler — Wed, 27 Feb 2019 16:56:57 GMT

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;

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

cckesler — Wed, 27 Feb 2019 16:58:33 GMT

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