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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.