Remove password from view

[Citrine10]

Hi there, my SAS dataset has a password on it and I have a view of that dataset.

How do I remove the password from the view and not the main dataset?

I have tried the below however when I try opening the file it gives an error message:

proc datasets library= mylib;

   modify dataset1 (pw=mypw/);

   run;

quit;

Error:

SQL View mylib.dataset1 could not be processed because at least one of the data sets, or views, referenced directly (or indirectly) by it could not be located, or opened successfully.

Alternatively, I can create the view again but I don't want it to create with a password.

[Quentin · Accepted solution]

Can you make a little example of what you mean?    If you have a dataset that is password-protected, and want to create a view which allows a user of the view to use read data from the dataset without specifying a password, then you can provide the password for the dataset when you create the view, e.g. :

data employee (pw=orange);
  name='foo' ;
  salary=1000 ;
run ;

data v_employee / view=v_employee;
   set employee(pw=orange drop=salary);
run;

*password required ;
proc print data=employee ;
run ;

*no password required ;
proc print data=v_employee ;
run ;

That is discussed in the docs in https://documentation.sas.com/doc/en/lrcon/9.4/n1wvhtsgtqf581n1pmxhenhzzkjj.htm .  Is that what you mean?  

It's also possible to create a view that has a password, independent of the password for the dataset that it accesses.  In that cases, you can use PROC DATASETS to remove the password from the view.  So below I create a view with a password, and then use PROC DATASETS to remove the password from the view.

data class (pw=foo1) ;
  set sashelp.class ;
run ;

proc sql ;
  create view v_class(pw=foo2) as select * from class(pw=foo1) ;
quit ;

*password for the view required ;
proc print data=v_class ;
run ;

proc datasets library=work ;
  modify v_class(pw=foo2/);
quit ;

*no password reqeuired;
proc print data=v_class ;
run ;

[Citrine10]

hi there,
So I have a dataset with a password on it.
When I create the view, I don't want any password on it.
I have tried your suggestion of:
proc datasets library=work ;
modify v_class(pw=foo2/);
quit ;

however when I use your suggestion and open the view in another SAS session I still get the same error message: SQL View mylib.dataset1 could not be processed because at least one of the data sets, or views, referenced directly (or indirectly) by it could not be located, or opened successfully.

I want the password to be completely removed from the view no matter how I open the view and at any time. This is so multiple users can use the view instead of the main dataset.

[Tom]

Are you sure this issue is the password and not just that the view doesn't work the way you are trying to use it?

For example if the view references a libref named MYLIB but the current session has no libref named MYLIB the view will not work.

[Citrine10]

yes the issue is the password. I have correctly assigned the libref in a new session and the view doesn't open. Thus my reason for asking for assistance 🙂

[Quentin]

I think maybe you have something like:

data class (pw=foo1) ;
  set sashelp.class ;
run ;

proc sql ;
  create view v_class(pw=foo2) as select * from class ; *the view was created without specifying password for work.class;
quit ;

proc datasets library=work ;
  modify v_class(pw=foo2/);
quit ;

proc print data=v_class ;
run ;

In that case work.class has a password.  When you created the view v_class you did not specify the password for work.class.  So even if you remove the password for v_class, you still need to specify the password for work.class when you use the view.

Running the above code replicates your error message:

1
2    data class (pw=XXXX) ;
3      set sashelp.class ;
4    run ;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.

5
6    proc sql ;
7      create view v_class(pw=XXXX) as select * from class ;
NOTE: SQL view WORK.V_CLASS has been defined.
7  !                                                         *the view was created without specifying

7  ! password for work.class;
8    quit ;

9
10   proc datasets library=work nolist;
11     modify v_class(pw=XXXX/);
12   quit ;


13
14   proc print data=v_class ;
ERROR: Invalid or missing READ password on member WORK.CLASS.DATA.
ERROR: SQL View WORK.V_CLASS could not be processed because at least one of the data sets, or views,
       referenced directly (or indirectly) by it could not be located, or opened successfully.
15   run ;

NOTE: The SAS System stopped processing this step because of errors.

If the under-lying dataset has a password, you need to specify the password for that dataset when either when you create the view, or when you use the view.

If that doesn't help, can you you make a little example (with a new dataset and a new view) that replicates the problem, and post the code and log from your example?

[Citrine10]

I understand that I need to specify the underlying password for the dataset when I create the view. The problem is the password remains on the view such that when I open the view in another session, it is password protected. I don't want the view to be password protected. I have tried the 'modify' clause but that didn't remove it because when I open the view in another session, it gives an error message.

[Quentin]

---

@Citrine10 wrote:
I understand that I need to specify the underlying password for the dataset when I create the view. The problem is the password remains on the view such that when I open the view in another session, it is password protected. I don't want the view to be password protected. I have tried the 'modify' clause but that didn't remove it because when I open the view in another session, it gives an error message.

---

Again, please post the code and log for a full example reproducing this problem.  So the code should:

1. Create a password protected dataset
2. Create a password-protected view of the dataset (specifying the password for the under-lying dataset)
3. Remove the password from the view
4. Run PROC PRINT to show that the password is still required

In all of my testing, I can't reproduce this problem.  In the examples I posted, I've been able to remove a password from a view using PROC DATASETS as you describe.

Note that the error message you report:

SQL View mylib.dataset1 could not be processed because at least one of the data sets, or views, referenced directly (or indirectly) by it could not be located, or opened successfully. 

Is not about a password on the view mylib.dataset1 itself.  It may result from a password on the underlying dataset, or other problems accessing the underlying dataset. Thus your PROC DATASETS step has (apparently) successfully removed any passwords that were on your view mylib.dataset1.

[Citrine10]

Correction on your message. This is what I want:
1. Create a password protected dataset
2. Create a view of the dataset (NOT password-protected)

[Quentin]

---

@Citrine10 wrote:
Correction on your message. This is what I want:
1. Create a password protected dataset
2. Create a view of the dataset (NOT password-protected)

---

Great, please show an example of code and log that is NOT working for you.  Everything I try works, e.g. below works:

data class (pw=foo1) ;
  set sashelp.class ;
run ;

proc sql ;
  create view v_class as select * from class(pw=foo1) ;
quit ;

proc print data=v_class ;
run ;

[Citrine10]

there is no log because all I do after using your code for example is I open a new session and point to the lib where the view is and it cant open without a password. Below error message: SQL View mylib.dataset1 could not be processed because at least one of the data sets, or views, referenced directly (or indirectly) by it could not be located, or opened successfully.

[Quentin]

There must be code and log, because none of the code I've posted creates a permanent view.  So you wouldn't be able to use a work view in another session.

In order to help debug this problem, you need to have a reproducible problem.  For example, a small reproducible example might be a program makeview.sas which creates a password-protected sas dataset mylib.foo and creates a permanent view  mylib.v_foo with no password.  And you run that program and get a clean log, and the view is created.  Then you open program useview.sas in a new SAS session and you attempt to use the view and get the error.  In that case, I'm asking you to post the code for makeview.sas and useview.sas and the log you get from running both programs.

[Citrine10]

I think you are misunderstanding. I don't use a work lib.

I use my own library path stored on my machine and when I try opening the view in a new session from that library, I get the error. There is no log when trying to open the dataset etc. as I use the file menu options.

[Quentin]

What application are you using?  i.e. SAS Display Manager ("Windows SAS"), Enterprise Guide, SAS Studio, DI Studio, etc?

You are running code to create the permanent SAS dataset that is password protected and create the permanent view you want to be unprotected, right?  If so, please post that code, and the logs from running the code.  Showing that both the password protected dataset and the view were created successfully.  Please include the libname statements in the code. 

That would be a small, reproducible example.  Then  I could take your code, update the libname statement to point to C:\Junk on my PC, and I should be able to run the code to create a permanent dataset and a permanent view.  Then I can start a new SAS session and open the view, and see if I get the same error.

[Citrine10]

SAS EG.


libname mydata "/fin/feb22";

/*Add password to dataset*/
data mydata.feb01(pw=x3jk);
set mydata.feb01;
run;

libname myview "/vw_fin/feb_vw";

proc sql;
create view myview.febvw as
select *
from mydata.feb01(pw=x3jk);
run;