BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kimdukes77
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

4 REPLIES 4
Kurt_Bremser
Super User

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.

BrunoMueller
SAS Super FREQ

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

 

 

kimdukes77
Obsidian | Level 7

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!

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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