Hello
I have series of data set with two types: Tbl_A and Tbl_B
Each data set name end with YYMMDD (date).
For example:
Tbl_A190827 Tbl_B190827
Tbl_A190824 Tbl_B190824
Tbl_A190822 Tbl_B190822
Tbl_A190823 Tbl_B190823
Tbl_A190817 No have Tbl_B190817
I want to create a condition that If for specific date the pair doesn't exist then need to delete the data set.
For example: Tbl_A190817 exists but Tbl_B190817 so need to delete Tbl_A190817
For example: Tbl_B190807 doesn't exist but Tbl_A190807 exists so need to delete Tbl_A190807
proc sql noprint;
create table tablesa as
select
memname,
substr(memname,6) as date_c
from dictionary.tables
where libname = 'WORK' and substr(memname,1,5) = 'TBL_A';
create table tablesb as
select
memname,
substr(memname,6) as date_c
from dictionary.tables
where libname = 'WORK' and substr(memname,1,5) = 'TBL_B';
select 'WORK.'!!coalesce(a.memname,b.memname) into :to_delete separated by ' '
from tablesa a full join tablesb b
on a.date_c = b.date_c
where a.memname is missing or b.memname is missing;
quit;
proc delete data=&to_delete.;
run;
Hi @Ronein,
how about:
data
Tbl_A190827 Tbl_B190827
/*Tbl_A190824*/ Tbl_B190824
Tbl_A190822 /*Tbl_B190822*/
Tbl_A190823 Tbl_B190823
;
x = 1;
run;
%macro test(date=);
%if %eval(%sysfunc(exist(tbl_A&date.,data)) * %sysfunc(exist(tbl_B&date.,data))) = 0
%then
%do;
options NODSNFERR; /* to avoid warnings in log */
proc delete data = Tbl_A&date. Tbl_B&date.;
run;
options DSNFERR;
%end;
%mend test;
%test(date=190824)
%test(date=190827)
%test(date=190822)
%test(date=190823)
All the best
Bart
As an alternative approach I can offer you also my home made `kill()` function which deletes dataset or view by name (not by reference, like `fdelete` does)
All the best
Bart
/* tested on windows and linux */
options cmplib = _NULL_;
proc FCMP
outlib = work.f.p
;
function kill(lbds_ $);
length F $ 8 ds $ 32 lb $ 2048 lbds $ 41;
F = cats("_",put(datetime(),hex7.));
lbds = strip(lowcase(lbds_));
if lbds = '_last_' then lbds = lowcase(symget("syslast"));
ds = scan(lbds, -1, ".");
if index(lbds, ".")>0 then lb = pathname(scan(lbds, 1, "."));
else lb = pathname(ifc(LIBREF("user"),"work","user"));
/*put lbds lb ds;*/
array ext[3] $ 12 (".sas7bdat", ".sas7bndx", ".sas7bvew");
_RCV_ = 1;
do i = 1 to dim(ext);
_RC_ = filename(F, cats(lb, "/", ds, ext[i]), "disk");
_RCV_ = _RCV_ * fdelete(F);
end;
_RC_ = filename(F);
return(_RCV_);
endsub;
run;
options cmplib = work.f;
data
Tbl_A190827 Tbl_B190827
/*Tbl_A190824*/ Tbl_B190824
Tbl_A190822 /*Tbl_B190822*/
Tbl_A190823 Tbl_B190823
;
x = 1;
run;
%macro test(date=);
%if %eval(%sysfunc(exist(tbl_A&date.,data)) * %sysfunc(exist(tbl_B&date.,data))) = 0
%then
%do;
%put *%sysfunc(kill(tbl_A&date.))**%sysfunc(kill(tbl_B&date.))*;
%end;
%mend test;
%test(date=190824)
%test(date=190827)
%test(date=190822)
%test(date=190823)
Hi,
*Generate example data;
PROC DATASETS lib=work kill nolist;RUN;QUIT;
data Tbl_A190827;set sashelp.class;run;
/*data Tbl_B190827;set sashelp.class;run; /**/
data Tbl_A190824;set sashelp.class;run;
data Tbl_B190824;set sashelp.class;run;
data Tbl_A190822;set sashelp.class;run;
data Tbl_B190822;set sashelp.class;run;
data Tbl_A190823;set sashelp.class;run;
/*data Tbl_B190823;set sashelp.class;run;/**/
%MACRO DeleteNoPair();
%local memnames;
%*Determine tables not occurring in pair tbl_Adddddd,tbl_Bdddddd;
PROC SQL;
SELECT memname INTO :memnames SEPARATED BY ','
FROM sashelp.vtable
WHERE libname eq 'WORK'
GROUP BY substr(memname,6)
HAVING count(substr(memname,6)) ne 2
;
%put Deleted tables:&memnames.;
%*Delete tables found if any;
%if not %sysevalf(%superq(memnames)=,boolean) %then %do;
DROP TABLE &memnames.;
%end;
QUIT;
%MEND DeleteNoPair;
%DeleteNoPair;
- Cheers -
And another solution, this time using a hash-object:
data _null_;
set sashelp.vtable(keep=MemName LibName where=(LibName = 'WORK')) end=jobDone;
if _n_ = 1 then do;
declare hash h(dataset: 'sashelp.vtable(keep= LibName MemName where=(LibName="WORK"))');
h.defineKey('MemName');
h.defineDone();
call execute('proc datasets library=work nolist;');
end;
if prxmatch('/TBL_[AB]\d{6}/', MemName) then do;
substr(MemName, 5, 1) = ifc(substr(MemName, 5, 1) = 'A', 'B', 'A');
if h.check() ^= 0 then do; /* check returns non-zero if key is not in the hash-object */
call execute(catx(' ', 'delete', MemName, ';'));
end;
end;
if jobDone then do;
call execute('quit;');
end;
run;
The task is three-fold:
All three, however, can be done in a single SQL step:
data Tbl_A190827 Tbl_B190827
Tbl_B190824
Tbl_A190822
Tbl_A190823 Tbl_B190823
/* invalid date suffixes */
Tbl_xyz
Tbl_191301
Tbl_191035
;
run ;
proc sql noprint ;
select catx (".", libname, memname) into :drop separated by ","
from dictionary.tables
where libname = "WORK"
and put (memname, $5.) in ("TBL_A", "TBL_B")
and input (substr (memname, 6), yymmdd8.) is not null
group substr (memname, 6)
having count (*) = 1
;
drop table &drop ;
quit ;
The log reports:
NOTE: Table WORK.TBL_A190822 has been dropped. NOTE: Table WORK.TBL_B190824 has been dropped.
Kind regards
Paul D.
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.