topic Re: How to get datasets' name and creation time?! in SAS Programming

How to get datasets' name and creation time?!

hellohere — Thu, 27 Jan 2022 00:48:50 GMT

Using tons of macros to process gezillion data, datasets are created in middle which are to delete.

So I plan to use a macro to do so. The macro need get the list of datasets, under work directory, and the creation time.

Based to the creation time, between the macro run starting and ending time, to remove datasets created in middle.

Anyone can help? To get the list of datasets in work and their creation time?!

Re: How to get datasets' name and creation time?!

Tom — Thu, 27 Jan 2022 01:19:09 GMT

Do you mean something like this?

%macro test;
%local start_dt dslist;
%let start_dt = %sysfunc(datetime());

/* Make some datasets */
data class; set sashelp.class; run;

proc sql noprint;
select nliteral(memname) into :dslist separated by ' '
from dictionary.tables
where libname='WORK'
  and modate >= &start_dt
;
quit;

%if (&sqlobs) %then %do;
proc delete data=&dslist;
run;
%end;
%mend test;

Let's try it. First lets make a dataset we want to keep. Then run it and see what datasets it decides to delete.

data xyz;
 set sashelp.class;
run;

options mprint;
%test;

Results:

1439  data xyz;
1440   set sashelp.class;
1441  run;

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


1442
1443  options mprint;
1444  %test;
MPRINT(TEST):   data class;
MPRINT(TEST):   set sashelp.class;
MPRINT(TEST):   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.01 seconds


MPRINT(TEST):   proc sql noprint;
MPRINT(TEST):   select nliteral(memname) into :dslist separated by ' ' from dictionary.tables where libname='WORK' and modate >=
1958847449.313 ;
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
MPRINT(TEST):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


MPRINT(TEST):   proc delete data=CLASS;
MPRINT(TEST):   run;

NOTE: Deleting WORK.CLASS (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Re: How to get datasets' name and creation time?!

Reeza — Thu, 27 Jan 2022 01:19:55 GMT

Query the table sashelp.vtable/dictionary.table.

It has the data set names and creation date/times (memname is table name, crdate is creation date).

Alternate suggestion - create a library named "user" at the top of the library.

By default, when SAS sees that library, tables will default to that location instead of work.

Delete entire library at end of macro. Done.

@hellohere wrote:

Using tons of macros to process gezillion data, datasets are created in middle which are to delete.

So I plan to use a macro to do so. The macro need get the list of datasets, under work directory, and the creation time.

Based to the creation time, between the macro run starting and ending time, to remove datasets created in middle.

Anyone can help? To get the list of datasets in work and their creation time?!

Re: How to get datasets' name and creation time?!

Reeza — Thu, 27 Jan 2022 01:24:17 GMT

FYI - my actual solution to this problem is to use _ in front of any temp tables in my processes. Then at the end I can use a wildcard to delete all tables that start with _ prefix using proc datasets.

proc datasets lib=work nodetails nolist;
delete _:;
run; quit;

Re: How to get datasets' name and creation time?!

hellohere — Thu, 27 Jan 2022 08:39:06 GMT

Thanks

Re: How to get datasets' name and creation time?!

hellohere — Thu, 27 Jan 2022 08:39:48 GMT

you should be my SAS coach.

Re: How to get datasets' name and creation time?!

hellohere — Thu, 03 Mar 2022 11:23:14 GMT

your lines work well. Why proc delete is not?! 7836 proc delete data=_:; run; - 22 200 ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, -, '.'. ERROR 200-322: The symbol is not recognized and will be ignored.

Re: How to get datasets' name and creation time?!

Reeza — Thu, 03 Mar 2022 17:13:48 GMT

Read the documentation. What does it say under DATA for PROC DELETE?

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n1469bsvzgd33jn0z4xecxmx2bgo.htm