Remove password from view

[Tom]

---

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

[Citrine10]

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.

[Tom]

The code you posted does NOT set a password on the view.  

But the step that creates the view does need to know the password for the dataset.  The only way to not require that is to remove the password from the dataset.

[Quentin]

In the second session, did your run both libname statements before clicking on File -> Open to open the view?

What makes you think that there is a password on the view? Did you actually get an error message about a password?

In the second session, can try to access the view using code, and show that code and the log?  In the second session, the code will need to run both libname statements.

[Citrine10]

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.

[Tom]

So your example program made TWO librefs. One to point to where to find the dataset and one to point to where to find the view.

In your new session you need to make both librefs before you can use the view.

Or you could tell the view to point directly at the file so you don't need to the second libref.

libname xxx 'c:\downloads';
data xxx.class(pw=abc123); set sashelp.class; run;
proc sql;
  create view v_class as
  select * from "%sysfunc(pathname(xxx))\class"(pw=abc123)
  ;
quit;
data test; 
  set v_class;
run;
libname xxx clear;
data test;
 set v_class;
run;

Or by adding a USING clause to the view definition:

proc sql;
  create view v_class as
  select * from xxx.class(pw=abc123)
  using libname xxx 'C:\downloads'
  ;
quit;

[Citrine10]

In the new session I dont want to write any code as the view is meant to be accessible to people that do not know the password, They want to directly open it in SAS on a new session successfully without it requiring a password

[Quentin]

---

@Citrine10 wrote:
In the new session I dont want to write any code as the view is meant to be accessible to people that do not know the password, They want to directly open it in SAS on a new session successfully without it requiring a password

---

The new session doesn't require a password.

But it does require that both librefs exist.  Unless you use one of the methods Tom just posted to create the view which doesn't depend on a libref.

Note if you try the test code you posted but delete all the bits about passwords when you create the permanent dataset and the permanent view, you should get the same error.  The error message is not about passwords, it's about the libref not existing in your second session.

[Quentin]

Please post the full log you get from starting the second session and submitting:

libname mydata "/fin/feb22";

libname myview "/vw_fin/feb_vw";

proc print data=myview.febvw;
run;

[Tom]

If the goal is just to let people view the data, but prevent them from modifying it, why not just make the dataset with an ALTER password instead of a regular password.

Example:

79   data class(alter=XXXXXX); set sashelp.class; 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.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


80   data test;
81     set xxx.class;
82   run;

NOTE: There were 19 observations read from the data set XXX.CLASS.
NOTE: The data set WORK.TEST has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


83   data class;
84    set class;
85    age=age+10;
86   run;

ERROR: Invalid or missing ALTER password on member WORK.CLASS.DATA.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           2.53 seconds
      cpu time            0.06 seconds

[Tom]

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.

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

[JosvanderVelden]

How will the view be used?
You can create a PROC SQL view from password-protected SAS data sets without specifying their passwords. Use the view and you are prompted for the passwords of the SAS data sets named in the FROM clause.

[Quentin]

@Citrine10 , did you find a solution to your problem?  If so, could you please either mark the correct solution, if you found the correct solution in this thread, or add the solution and mark it as the answer?

I noticed that your recent threads have ended without an accepted answer (e.g. https://communities.sas.com/t5/New-SAS-User/yesterday-function-not-working/td-p/863445 https://communities.sas.com/t5/SAS-Programming/Copy-sas-dataset-files-from-one-directory-to-another/... ) The community works better when you close out a question by marking an answer as accepted.  This helps future community members who may have the same question, and also lets the community know that you are not looking for further answers.