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?!
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
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
Thanks
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?!
you should be my SAS coach.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.