USING LIBNAME in a VIEW

[Tom]

If you want to do the SQL view then the libref you use in the USING clause does not have look anything like the actual libref that exists.  The important part is that the PATH used there is the one you want them to notice.

proc sql;
create view "&outpath/class_sqlv" as
  select * 
  from x.class
  where sex='M'
  using libname x "&inpath"
;
quit;

proc print data="&outpath/class_sqlv";
  title "'&outpath/class_sqlv'";
run;

[samlac]

Hello,

I tried this:

%let path=c:\downloads;
libname dummy "&path" ;
data dummy.class ; set sashelp.class; run;

data dummy.class_v/ view=dummy.class_v;
  set "&path/class" ;
  if sex='M';
run;

this actually works great.

If I use my own tables and my own libname then it only work during the same session. If I close my current SAS session and try to open the view i created previously I get this error: Cannot open data set "central". failure loading ECI00001.Central.VIEW. Error detected during View Load request.

[yabwon]

Which version of sas do you use?

On my M8 I have:

1    %let path=R:\;
2    libname dummy "&path" ;
NOTE: Libref DUMMY was successfully assigned as follows:
      Engine:        V9
      Physical Name: R:\
3    data test;
4    set dummy.class_v;
5    run;

NOTE: View DUMMY.CLASS_V.VIEW used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

NOTE: There were 19 observations read from the data set R:\/class.
NOTE: There were 10 observations read from the data set DUMMY.CLASS_V.
NOTE: The data set WORK.TEST has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds

bart

[Sajid01]

Using libnames it should work as long as view's are supported. M8 is not necessary.

[samlac]

I have SAS 8.4

Per SAS Documentation:
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0v059azebcuepn1o8x4c7wi1pfs.htm
SQL Views with Embedded LIBNAME Statements
With SAS software, you can embed LIBNAME statements in the definition of an SQL view. This means that you can store a LIBNAME statement in an SQL view that contains all information that is required to connect to a DBMS. Whenever the SQL view is read, PROC SQL uses the embedded LIBNAME statement to assign a libref. After the view has been processed, PROC SQL unassigns the libref.

In this example, an SQL view of the Hadoop table Dept is created. Whenever you use this view in a SAS program, the Hadlib library is assigned. The library uses the connection information (user name, password, and data source) that is provided in the embedded LIBNAME statement.

ITs also stated thids for DATA SET:
Restrictions and Requirements
If you create a new view, and a view with the same name already exists in the library, then the old view is overwritten.

If you include global statements in a source program, SAS stores the DATA step view but not the global statements. Examples of global statements include the FILENAME, FOOTNOTE, LIBNAME, OPTIONS, and TITLE statements. When the view is referenced, execution can differ from usual DATA step processing.

[Tom]

Which just means if you code GLOBAL statements in the middle of the data step that is defining the VIEW they are NOT part of the view.  Which makes sense.  You probably shouldn't put GLOBAL statements into the middle of a step anyway as it will just confuse you about the actual order of operation.

Note there is not need to embed a LIBNAME definition in the middle of a data step view.  That is a feature they added to PROC SQL.  Which means that in data step views you can only "embed" access to SAS datasets or views.   

If you did need to embed a link to an external database you would need to make an SQL view. 

If you also need data step functionality then make two views and have the data step view reference the SQL view.

libname target "&path";
proc sql;
create view target.oracle_view as
  select * from x.table1
  using libname x oracle .... ;
;
quit;
data target.ds_view / view=target.ds_view;
  set "&path/oracle_view.sas7bvew" ;
run;

[yabwon]

btw. how's SQL version?

In your session:

libname dummy "C:\test" ;
data dummy.class ; 
set sashelp.class; 
run;


libname mySesion "C:\downloads";

PROC SQL;
        CREATE VIEW mySesion.Central AS
           SELECT   *
            FROM x.class
            where sex='M'

        USING LIBNAME x "C:\test";
        ;
QUIT;

in bran new one:

libname newSas "R:\downloads";
proc print data=newSas.central;
run;

bart

[Kurt_Bremser]

First of all,

.sas7dbat

is not a valid extension for any SAS file. The valid extension for a SAS dataset file is

.sas7bdat

Next, SAS dataset filenames must be all lowercase, particularly on the case-sensitive UNIX system you apparently use.

Second, SAS SQL does not have a USING clause.

Third, you can't do anything with SAS datasets without running SAS code. What you can do is to make it easier for users to run the code, e.g. by storing it in an easily accessible .sas program file. Or, if you have a BI Server, you can define stored processes which are easily run from a web interface. It depends on the way your users run the SAS system (locally installed, or client/server with either SAS Studio or Enterprise Guide).

[yabwon]

"Second, SAS SQL does not have a USING clause." - it does.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n0nolnbokay91in1gouzgw3xzl5e.htm

Bart

[Kurt_Bremser]

I stand corrected. Funny that SAS did not document it as a clause in the list of SQL elements (where I looked).

[yabwon]

You are right, I would make it more "visible" too.  It took me a bit to find it a few years ago when I needed this kind of feature.