topic Re: How to stop sas view to access all datasets in SAS Programming

How to stop sas view to access all datasets

Gexern — Wed, 27 Nov 2019 15:00:23 GMT

Dear SAS community

I'm working on a server, where I retrieve the data I need from a view. The view is a panel data, where we observe different persons over several years. What I discovered is that the view is connected to several other, smaller data sets, where there is one data set for each year. I can see this from the log as it shows me:

NOTE. There were 100000000 observations read from the data set example.example2000
NOTE. There were 100000000 observations read from the data set example.example2001
NOTE. There were 100000000 observations read from the data set example.example2002
...
NOTE. There were 100000000 observations read from the data set example.example2020

When I retrieve data from this view, I seldomly need all years, say 2000-2010. Since there are several million observations, I would like to stop SAS from reading from all years, but with a where statement I can still see from the log that it reads all the datasets. Example of my code is:

proc sql ;
    create table my_table as
        select * from example.example
    where year>=2000 and year<=2010 ;
quit ;

Something that quadrouples the run time is if I put the where statement in the "from", if I want to do some additional calculations, An example is shown below:

proc sql ;
    create table my_table as
        select this as that, this_1 as that_2 ...
        from (select this, this_1 ..., from example.example 
                 where year>=2000 and year<=2010) ;
quit ;

What I would like to know is if it is possible for me to tell SAS when I read from a view which data sets it should (or should not) access? So I could tell it something like (pseudocode):

proc sql ;
    create table my_table as
        select * from example.example
    where view acces databases: example.example2000, example.example2001 ... example.example2010;
quit ;

Re: How to stop sas view to access all datasets

Kurt_Bremser — Wed, 27 Nov 2019 15:03:22 GMT

A specific attribute of a view is that it "hides" the underlying tables to make access easier.

If you already know the tables involved, define your own view with only those and use that.

Re: How to stop sas view to access all datasets

Gexern — Wed, 27 Nov 2019 15:07:57 GMT

Ah yes, that would be ideal. But I have only access to the views, access to other folders on the server is restricted.

Re: How to stop sas view to access all datasets

mkeintz — Wed, 27 Nov 2019 15:21:09 GMT

Then the designers of your SAS server have created a problem that only they can mitigate.

Re: How to stop sas view to access all datasets

Kurt_Bremser — Wed, 27 Nov 2019 15:23:27 GMT

SAS has two types of views: SQL views and data step views. Both can be described, and both require access to the underlying tables to work.

If you are not allowed to access dataset A, view B that tries to read dataset A will also fail.

Re: How to stop sas view to access all datasets

Quentin — Wed, 27 Nov 2019 15:37:57 GMT

As Kurt said, since the view is working, you must have read access to the underlying SAS datasets (at the OS level).

That said, since you're on a SAS server, it's possible that your libref is a metadata library, and is only showing you the names of SAS tables (views and datasets) that have been registered in the metadata. It's possible that the underlying SAS datasets have not been registered in the metadata, so you wouldn't see them.

If you switch to writing your own native OS libname statement, you should be able to see the underlying SAS datasets.

There are, I think, lock-down modes which an admin can set to preclude you from being able to write your own libname statement. If they've done that, then you may be forced to have a discussion with them.

Re: How to stop sas view to access all datasets

Gexern — Wed, 27 Nov 2019 15:39:00 GMT

Im not sure what to tell you, but I don't have access to the underlying tables. The views are called "SAS Data Set View".

If I go into the folder that have all the datasets that the view access, I can see all the datasets. But if I try to open/access one of them, I am asked for a password, which I am not allowed to get. So my only access to the underlying data is through the view. I guess there is no solution to the problem then sadly.

Re: How to stop sas view to access all datasets

Kurt_Bremser — Wed, 27 Nov 2019 15:42:51 GMT

See how to describe a data step view:

/* create the view */
data class/view=class;
set sashelp.class;
run;

/* describe the view */
data view=class;
describe;
run;

Log from this:

73         /* create the view */
 74         data class/view=class;
 75         set sashelp.class;
 76         run;
 
 NOTE: DATA STEP view saved on file WORK.CLASS.
 NOTE: A stored DATA STEP view cannot run under a different operating system.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 77         
 78         /* describe the view */
 79         data view=class;
 80         describe;
 81         run;
 
 NOTE: DATA step view WORK.CLASS is defined as:
 
 data class/view=class;
    set sashelp.class;
 run;
 
 
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.01 seconds

Re: How to stop sas view to access all datasets

Tom — Wed, 27 Nov 2019 15:53:03 GMT

Sounds like they are using the view to hide password from you.

Perhaps you can ask them to create more views. Either views for each year. Or multiple year views that cover viewer years and you can pick the one that works best for you. Or perhaps just totally rebuild the system using some that will work better for your use case. Like putting the data into an actual database. Or at least rebuild the SAS datasets using the SPD engine so that it could be configured to use YEAR as a way to partition the data so that when you filter by year you only have to read the disk blocks that have those year's data.

Re: How to stop sas view to access all datasets

Gexern — Wed, 27 Nov 2019 15:53:50 GMT

So this is interesting.

The view I am accessing is a SQL-view, so if I do

proc sql ;
    describe view example.example ;
quit ;

It tells me something like this:

SQL view example.example is defined as:
select var1, var2, ..., varX from example.exampleA
using libname example "some_path" ;

So the path "some_path" is where I found all the data sets that I need a password to access. But it says it doesn't read from them, but from another view called example.exampleA.

When I find this example.exampleA it is also a view, but a Data step view. If i try to do:

data view=example.exampleA ;
    describe ;
run ;

I get the some error like "ERROR: Libref example is not assigned" and "FATAL: Undetermined execution error detected in the execution of the DATA step program. Aborted during the AUTO-LOADING STORED PROGRAM pahse."

So I guess they REALLY don't want me to access the datasets themselves, but it is interesting that the SQL-view reffres to a DATA-view that I again cannot access.

Re: How to stop sas view to access all datasets

Quentin — Wed, 27 Nov 2019 15:54:34 GMT

Interesting. Devious admins.

I suppose it's possible they applied a SAS native password to the underlying datasets, and provide the password when they define the view. With that, I think you'd be out of luck for reading the datasets.

They could have done something like:

data have (pw=foo);
  set sashelp.class ;
run ;

data haveview /view=haveview ;
  set have (pw=foo);
run ;

data view=haveview ;
  describe ;
run ;

proc print data=haveview ;
run ;

proc print data=have ;
run ;

With that, you can read HAVEVIEW, but you can't read HAVE. And if you describe the view, SAS shows you that there is a password, but (sensibly) doesn't let you see it in the log:

17   data view=haveview ;
18     describe ;
19   run ;

NOTE: DATA step view WORK.HAVEVIEW is defined as:

data haveview /view=haveview ;
   set have (pw=XXX);
run ;

I think Mark is right, the admins might have you in too tight a box to do what you want.

Re: How to stop sas view to access all datasets

Gexern — Wed, 27 Nov 2019 16:00:59 GMT

Yes, I have given up!

Re: How to stop sas view to access all datasets

Tom — Wed, 27 Nov 2019 16:07:53 GMT

So that is great detective work. Keep going.

When you use the USING clause in an SQL view the libref (EXAMPLE) used there does not really need to match a real libref.

select var1, var2, ..., varX from example.exampleA
using libname example "some_path" ;

So take the path from the code and use it to define another libref. Then try to see the data step view definition using your new libref.

libname XXX "some_path" access=readonly ;
data view=XXX.exampleA ;
    describe ;
run ;

Re: How to stop sas view to access all datasets

Gexern — Wed, 27 Nov 2019 16:39:51 GMT

Hmm, I am unable to reproduce the error I got, when I now run:

data view=example.exampleA ;
    describe ;
run ;

I get:

NOTE: No source statements were found in example.exampleA.view

If I run:

libname XXX "some_path" access=readonly ;

proc contents data=XXX.example2000 ;
run ;

I get: "READ ACCESS DENIED" and asked for password.

But I can run proc contents on the Data Step View:

proc contents data=example.exampleA ;
run ;

But it tells me tha same variables if i'd done it on example.example. The only difference between the SQL view (example.example) and the Data Step view (example.exampleA) is this:

ENGINE: SASDSV
DATA Step view type: INPUT

Re: How to stop sas view to access all datasets

Tom — Wed, 27 Nov 2019 16:53:04 GMT

Looks like they used the SOURCE option to deliberately hide the source code for the view.

SOURCE=source-option

specifies one of the following source options:

SAVE

saves the source code that created a stored compiled DATA step program or a DATA step view.

ENCRYPT

encrypts and saves the source code that created a stored compiled DATA step program or a DATA step view.

Tip: If you encrypt source code, use the ALTER password option as well. SAS issues a warning message if you do not use ALTER.

NOSAVE

does not save the source code.

CAUTION:

If you use the NOSAVE option for a DATA step view, the view cannot be migrated or copied from one version of SAS to another version.

Default: SAVE

Re: How to stop sas view to access all datasets

Gexern — Wed, 27 Nov 2019 17:10:48 GMT

Yes, I do understand why they want to protect the data sets, as if they are changed disaster ensues. But would it be damaging to have read, but not write access? Since if I had read access I could maybe get data from them, but not make any changes?

Re: How to stop sas view to access all datasets

Quentin — Wed, 27 Nov 2019 17:24:00 GMT

Perfectly reasonable to ask for read access, particularly as you have it through the view already. Of course since they've got through serious lengths to hide the view from you, it's possible they're doing some trickery in the view that they don't want to let you avoid (e.g. masking sensitive data).

Re: How to stop sas view to access all datasets

Gexern — Wed, 27 Nov 2019 17:30:12 GMT

Ah, yes, that would make sense. As it is indeed sensitive data and they "scramble" it before I see it. So it might be that!

Re: How to stop sas view to access all datasets

mkeintz — Wed, 27 Nov 2019 17:34:57 GMT

What you might do is get them to make a view for each yearly dataset (they were yearly datasets, iirc). They could use a single common code to mask what they want to mask in each such view. Then you could just select from a union of the subset of view of interest to you.

It would be a minimal one-time (or one-time per year) effort on the db manager's part. And not only would you save time, but they would conserve a lot of computing resources for use by your colleagues.