topic Re: List sas datasets within a library when encrypted in SAS Programming

List sas datasets within a library when encrypted

kimdukes77 — Thu, 09 Mar 2017 10:05:12 GMT

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

Re: List sas datasets within a library when encrypted

Kurt_Bremser — Thu, 09 Mar 2017 10:18:01 GMT

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.

Re: List sas datasets within a library when encrypted

BrunoMueller — Thu, 09 Mar 2017 10:31:54 GMT

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

Re: List sas datasets within a library when encrypted

kimdukes77 — Thu, 09 Mar 2017 12:45:45 GMT

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!

Re: List sas datasets within a library when encrypted

ballardw — Thu, 09 Mar 2017 18:16:08 GMT

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;