Verifying whether a Dataset has a password assigned.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Verifying whether a Dataset has a password assigned.

I've been researching the identification of SAS datasets which have a password assigned.  The Enterprise Guide help provides the following example syntax:

  • This example checks whether a data set is protected with a password.

data _null_;
   dsid=open("mydata");
   pw=attrn(dsid,"pw");
   if pw then put "data set is protected";
run;

If I run this code against a dataset which already has a password assigned, it fails with the following log entry:

NOTE: Argument 1 to function ATTRN at line 18 column 7 is invalid.
DSID=0 PW=. _ERROR_=1 _N_=1

Note that in the log entry above, the value of "DSID" is zero and the value of "PW" is missing.  This indicates that the dataset was not opened.  I suspect that this is because the password is active and prevents READ access?

If this is correct, then it appears to me that the Online Help Documentation is incorrect as it states that a password protected dataset would return the value of 1 (true) for the variable "PW".

As a test, I ran the same code against the same dataset, after I had removed the password.  The process ran correctly, without errors and returned the expected values as advised in the online help. ie: "DSID" = 1, "PW" = 0.

Has anyone used the above syntax?  Has anyone experienced the same issue?


Accepted Solutions
Solution
‎10-30-2013 12:08 AM
Respected Advisor
Posts: 3,156

Re: Verifying whether a Dataset has a password assigned.

Ok, by knowing your purpose, then next move is clear: avoid irritating targeted table (as it will trigger PW event) while retrieving needed information,  Using Dictionary tables:

data _null_;

  set SASHELP.VTABLE;

    where libname='WORK' AND MEMNAME='MYDATA';

IF PROTECT='---' THEN PUT " NOT ENCRYPT";

ELSE if protect=:'R' then PUT "ENCRYPTED";

RUN;

HTH,

Haikuo

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: Verifying whether a Dataset has a password assigned.

I haven't tried the method you used. But  Proc contents will show you whether a table is encrypted or not.

update: I just did a  little experiment, it seems if you feed the correct password, it will generate the expected outcome.

Occasional Contributor
Posts: 6

Re: Verifying whether a Dataset has a password assigned.

Thank you for your response and your update.  I'd like to make 2 comments.

  1. The reason I wish to use the ATTRN function is to determine the protection status of the dataset as part of an automated process, then branch to the appropriate code steps based on the return code.  The fact that you achieved a result, only by providing the password in the first place, kind of defeats the purpose of it all.  I want to find out if a password is present, whether I know it or not.
  2. If a Proc Contents is run against a password protected dataset, without providing the password, the process will fail:

                      ERROR: Invalid or missing READ password on member mydataset.DATA.

        Running a Proc Contents will not fulfil my process requirements and is not suitable in this particular application.


Solution
‎10-30-2013 12:08 AM
Respected Advisor
Posts: 3,156

Re: Verifying whether a Dataset has a password assigned.

Ok, by knowing your purpose, then next move is clear: avoid irritating targeted table (as it will trigger PW event) while retrieving needed information,  Using Dictionary tables:

data _null_;

  set SASHELP.VTABLE;

    where libname='WORK' AND MEMNAME='MYDATA';

IF PROTECT='---' THEN PUT " NOT ENCRYPT";

ELSE if protect=:'R' then PUT "ENCRYPTED";

RUN;

HTH,

Haikuo

Occasional Contributor
Posts: 6

Re: Verifying whether a Dataset has a password assigned.

Okay, that does the job!  For some reason, it takes approximately 1 minute to execute even though there are only 17 datasets in the library, but that's not so important.  I can modify that data step to call some macros according to the status of "PROTECT".

Thanks Haikuo!

I'm going to mark that as "Correct Answer".

Cheers!

David.

Respected Advisor
Posts: 3,156

Re: Verifying whether a Dataset has a password assigned.

Looks like that you don't mind dealing with macros, the following approach is known faster than sashelp.vtables, using a Macro variable.

proc sql NOPRINT;

  select case when protect = '---' then 'Not Encrypted' when substr(protect,1,1)='R' then 'Encrypted' end into :status

from dictionary.tables

where libname='WORK'

AND MEMNAME='MYDATA';

QUIT;

%PUT &STATUS;

Haikuo

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 865 views
  • 0 likes
  • 2 in conversation