BookmarkSubscribeRSS Feed
RussellAlmeida
Calcite | Level 5

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

11 REPLIES 11
FriedEgg
SAS Employee

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
Calcite | Level 5

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
Super User

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

FriedEgg
SAS Employee

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
Rhodochrosite | Level 12

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
SAS Employee

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
Rhodochrosite | Level 12

the example code in the documentation uses both FROM and USING

FriedEgg
SAS Employee

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
Rhodochrosite | Level 12

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

LinusH
Tourmaline | Level 20

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.

Data never sleeps
FriedEgg
SAS Employee

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 2313 views
  • 2 likes
  • 5 in conversation