topic Remove password on view in SAS Data Management

Remove password from view

Citrine10 — Tue, 06 Jun 2023 11:07:04 GMT

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.

Remove password on view

Citrine10 — Tue, 06 Jun 2023 12:47:21 GMT

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. it is very important that the main dataset has the password.

Re: Remove password from view

Quentin — Tue, 06 Jun 2023 12:52:59 GMT

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 ;

Re: Remove password from view

Citrine10 — Tue, 06 Jun 2023 12:59:40 GMT

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.

Re: Remove password from view

Tom — Tue, 06 Jun 2023 13:04:31 GMT

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.

Re: Remove password from view

Citrine10 — Tue, 06 Jun 2023 13:07:47 GMT

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 🙂

Re: Remove password from view

Quentin — Tue, 06 Jun 2023 13:21:47 GMT

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?

Re: Remove password from view

Citrine10 — Tue, 06 Jun 2023 13:25:47 GMT

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.

Re: Remove password from view

Quentin — Tue, 06 Jun 2023 13:50:24 GMT

@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:

Create a password protected dataset
Create a password-protected view of the dataset (specifying the password for the under-lying dataset)
Remove the password from the view
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.

Re: Remove password from view

Citrine10 — Tue, 06 Jun 2023 13:56:22 GMT

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)

Re: Remove password from view

Quentin — Tue, 06 Jun 2023 14:02:59 GMT

@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 ;

Re: Remove password from view

Citrine10 — Tue, 06 Jun 2023 14:06:57 GMT

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.

Re: Remove password from view

Quentin — Tue, 06 Jun 2023 14:16:52 GMT

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.

Re: Remove password from view

Citrine10 — Tue, 06 Jun 2023 14:20:12 GMT

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.

Re: Remove password from view

Quentin — Tue, 06 Jun 2023 14:45:23 GMT

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.

Re: Remove password from view

Citrine10 — Tue, 06 Jun 2023 14:57:43 GMT

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;

Re: Remove password from view

Tom — Tue, 06 Jun 2023 14:58:07 GMT

Also make sure that the SAS version used in the second session matches the first. I am not sure that CODA will work for views.

Re: Remove password from view

Tom — Tue, 06 Jun 2023 15:04:22 GMT

@Citrine10 wrote:
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;

Your second program is missing the LIBNAME statement for the libref named MYDATA.

But that should give a slightly different error message:

33   libname xxx clear;
NOTE: Libref XXX has been deassigned.
34   data test;
35    set v_class;
ERROR: Libref XXX is not assigned.
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.
36   run;

Re: Remove password from view

Citrine10 — Tue, 06 Jun 2023 15:02:17 GMT

its got it there libname mydata "/fin/feb22";

anyway, when I run the code, it sets the password on the view and the dataset. I only want the password on the dataset. Not the view.

Re: Remove password from view

Citrine10 — Tue, 06 Jun 2023 15:06:26 GMT

yes this is the message i get when opening it in a new session: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.