Creating a view from BASE SAS Library pointing to SPDS Library

[RussellAlmeida]

Hi Support,

I am working for a SAS Project where we have tables in SPDS and SAS libraries.

The end old users are configured to connect to BASE SAS Libraries.

The new solution is generating datasets on SPDS Platform.

Due to some reasons(space, time, network etc) the customer has decided not to copy over the data from SPDS to BASE SAS.

At the same time the customer wants the old end users to still keep using the access to tables via BASE SAS Library.

I want to create a view which is visible to the old users via BASE SAS but the actual table resides on SPDS Library..

Please suggest.

Russell

[FriedEgg]

The following is untested as a warning.  I am also unsure of the performance ramifications of this practice.

proc sql;

create table have as

select libname, memname

   from sashelp.vstable

  where libname in ( select libname from sashelp.vlibnam where engine='SPDS' );

quit;

libname base '/temp';

data _null_;

set have;

  call execute('proc sql; create view base.v_' || strip(memname) || ' as select * from ' || catx('.', of libname memname) || ';');

run;

[RussellAlmeida]

Hi,

As far as we have the view created on the same environment it is OK.

Here i need to create a view which is pointing to a table in SPDS.

When i create the view, it works fine, i can open it but as soon as i close my SAS Session, and re-open it, the view fails giving the error 'Libname does not assigned'.

Russell

[Ksharp]

It looks like the old end users for Base library have not proper right to read SPDS library.

[FriedEgg]

The SPDS library would always need to exist in order for a view to access it.  You could add the libname assignment as a auto executed piece of code on session startup if you have access to the end-user config files.

[Peter_C]

then you must create an SQL-view, because that will allow an sql query to reach through to the SPDS environment for any optimisation available from indexes (that a data step view would not)!

The other point is the USING clause which proc sql uses to dynamically define within the sql statement, a libname connection to a dbms (see the end of the CREATE VIEW Statement http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473674.htm ). Hopefully, this applies also for accessing SPDS and will save you maintaining the connection to SPDS any longer than neccessary.

[FriedEgg]

Thanks Peter, this is a good point.  Instead of the from statement the using clause could be used inorder to assign the library upon use of the view instead of needing to be a preexisting definiton.  This much better fits the desired functionality of your views.

[Peter_C]

the example code in the documentation uses both FROM and USING

[FriedEgg]

You are correct again, I mispoke.  You use the from statement as usualy and embed the library reference data with the using clause.

%let spds_lib=SPDS;

proc sql;

create table have as

select libname, memname

   from sashelp.vstable

  where libname in "&spds_lib";

quit;

libname base '/temp';

data _null_;

set have;

  call execute('proc sql; create view base.v_' || strip(memname) || ' as select * from ' || catx('.', of libname memname) || ' using libname in sasspds "domain-name" server="server" user="userid" password="password";');

run;

[Peter_C]

creating base sql views of every table in the SPDS library - very neat idea

[LinusH]

I'm late on this one, just stumbled it over. And this probably already no issue at your sight at this point.

But, anyway...

Why do the old end users need to access the data via a Base SAS library?

The whole point of different access engines is that the data looks the same to the users, can be used in the same way, independent of where the data is actually stored.

In rare cases, there are some programs especially written for certain engines. If this is the case, I guess it would reamain a problem even with a Base View scenario.

[FriedEgg]

The reason, as I remember for wanting to create the permanent views what basically that several data sets from a v9 engine library had been redone to use the spds server.  To make sure that programs and users expecting to use the old files did not encounter issues with the transition the OP decided the smoothest method would be to basically create a symbolic link between the two.  The other individuals interacting with the file then need none of the special knowledge of using and defining SPDS libraries and no legacy program break.  To me it makes sense during a transition period.