DATA Step, Macro, Functions and more

List sas datasets within a library when encrypted

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

List sas datasets within a library when encrypted

Is it possible to create a list of datasets within a SAS library when those datasets have been encrypted?

 

Code below:

 

proc sql noprint;
create table work.listout as select * from sashelp.vtable
where upcase(libname) = "STORE" and upcase(memname) contains upcase("&user_reference.")
order by modate asc
;
quit;

 

And encryption code here:-

 

data store.&fname. (ENCRYPT=AES ENCRYPTKEY=&pwdit.);
set datafile_values_raw;
run;

 

Thanks


Accepted Solutions
Solution
‎03-09-2017 07:46 AM
SAS Super FREQ
Posts: 709

Re: List sas datasets within a library when encrypted

Posted in reply to kimdukes77

Hi

 

You can use the DICTIONARY.MEMBERS dictionary table to list the SAS datasets. Here is an example:

 

proc delete data=work.enc;
run;

data work.enc (ENCRYPT=aes encryptkey=sugus);
  set sashelp.class;
run;

proc sql noprint;
  create table work.members as
  select * from dictionary.members
  where libname = "WORK"
  ;
quit;

If you want to use the AES encryption, then I would recommend to use Metadata Bound Libraries. Using the MBL you will never have to use the password in your program.

 

Bruno

 

 

View solution in original post


All Replies
Super User
Posts: 7,854

Re: List sas datasets within a library when encrypted

Posted in reply to kimdukes77

When using SQL, it's better to access dictionary.tables directly instead of through the view sashelp.vtable. The view reads all datasets in all currently assigned libraries before SQL can apply the where condition.

When using dictionary.tables, the where condition is immediately used so only the subset of datasets is accessed, making it much more performant when you have lots of datasets in your libraries.

 

That said, I can't test your code as I don't have SAS/SECURE licensed. If your select fails because of encrypted datasets, you could try to extract the pathname from the library and use OS commands to get a directory listing. You won't get metadata about the structure and number of obs, but you can still get the physical size, owner, OS permissions and modification/access timestamps.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎03-09-2017 07:46 AM
SAS Super FREQ
Posts: 709

Re: List sas datasets within a library when encrypted

Posted in reply to kimdukes77

Hi

 

You can use the DICTIONARY.MEMBERS dictionary table to list the SAS datasets. Here is an example:

 

proc delete data=work.enc;
run;

data work.enc (ENCRYPT=aes encryptkey=sugus);
  set sashelp.class;
run;

proc sql noprint;
  create table work.members as
  select * from dictionary.members
  where libname = "WORK"
  ;
quit;

If you want to use the AES encryption, then I would recommend to use Metadata Bound Libraries. Using the MBL you will never have to use the password in your program.

 

Bruno

 

 

Occasional Contributor
Posts: 16

Re: List sas datasets within a library when encrypted

Posted in reply to Bruno_SAS

This works but doesn't return the timestamp for the dataset - date created is contained within the dataset name though so that can be used to sort and dedupe and retain the most recent verison.

 

Thanks!

Super User
Posts: 11,343

Re: List sas datasets within a library when encrypted

Posted in reply to kimdukes77

You may want to use dicitonary.tables as it has much more information about the tables.

This will show what you can find out about the datasets:

proc sql;
   describe table dictionary.tables;
quit;
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 187 views
  • 3 likes
  • 4 in conversation