topic Re: Delete data set in SAS Programming

Delete data set

Ronein — Tue, 14 Jan 2020 12:09:58 GMT

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

Re: Delete data set

Kurt_Bremser — Tue, 14 Jan 2020 12:32:04 GMT

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;

Re: Delete data set

yabwon — Tue, 14 Jan 2020 12:47:05 GMT

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

Re: Delete data set

yabwon — Tue, 14 Jan 2020 13:13:22 GMT

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)

Re: Delete data set

Oligolas — Tue, 14 Jan 2020 13:33:41 GMT

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;

Re: Delete data set

andreas_lds — Tue, 14 Jan 2020 14:59:05 GMT

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;

Re: Delete data set

hashman — Tue, 14 Jan 2020 22:18:21 GMT

@Ronein:

The task is three-fold:

find the memnames prefixed with Tbl_A and Tbl_B with the suffixes representing valid dates in the YYMMDD format
out of those, find the memnames whose suffixes have count=1 (i.e. they aren't paired)
drop the corresponding members from the library (e.g. WORK, if that is your library)

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.