Clear physical table with a table loader

[mike73]

Hi,

We have a job written by SAS consultunts, and there is a step we are trying to figure out. They used a table loader to clear a table, and we just cannot figure out how it works:

We've look at the sas code and there is nothing obvious that allows a table loader be used to clear a table.

Any ideas?

Thanks

[LinusH]

Just the picture of the won't tell.
How is that Table Loader configured?
What does the generated SAS code look like?

[mike73]

As a test, I replaced their table loader with a fresh one from the transformations tab, did nothing with (all columns mapped to each other by default) and the code generated is identical.

Code (i was requested to obfuscate column names)

/*==========================================================================* 
 * Step:            Clear Table                           A5AR4NKY.BX000UKT * 
 * Transform:       Table Loader (Version 2.1)                              * 
 * Description:                                                             * 
 *                                                                          * 
 * Source Table:    TRA_PRELIM - DEVPRE.TRANS_PRELIM      A5AR4NKY.BE0002BD * 
 * Target Table:    TRA_PRELIM - DEVPRE.TRANS_PRELIM      A5AR4NKY.BE0002BD * 
 *==========================================================================*/ 

%let transformID = %quote(A5AR4NKY.BX000UKT);
%let trans_rc = 0;
%let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 

LIBNAME DEVPRE BASE "E:\Development\Data\SASData\Prelim";
%rcSet(&syslibrc); 

%let SYSLAST = %nrquote(DEVPRE.TRANS_PRELIM); 

/* Runtime statistics macros  */ 
%etls_setPerfInit;
%perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Loader), metrNam6=_DISROWCNT, metrDef6=Count32)   ;

%global etls_sql_pushDown;
%let etls_sql_pushDown = -1;
option DBIDIRECTEXEC;

%global etls_tableExist;
%global etls_numIndex;
%global etls_lastTable;
%let etls_tableExist = -1; 
%let etls_numIndex = -1; 
%let etls_lastTable = &SYSLAST; 

/*---- Define load data macro  ----*/ 

/* --------------------------------------------------------------
   Load Technique Selection: Replace - EntireTable
   Constraint and index action selections: 'ASIS','ASIS','ASIS','ASIS'
   Additional options selections... 
      Set unmapped to missing on updates: false 
   -------------------------------------------------------------- */
%macro etls_loader;

   %let etls_tableOptions = ;
   
   /* Determine if the target table exists  */ 
   %let etls_tableExist = %eval(%sysfunc(exist(DEVPRE.TRANS_PRELIM, DATA)) or 
         %sysfunc(exist(DEVPRE.TRANS_PRELIM, VIEW))); 
   
   %if &etls_tableExist %then 
   %do;/* table exists  */ 
      %let etls_hasPreExistingConstraint=0; 
      
      proc datasets lib = work nolist nowarn memtype = (data view);
         delete etls_commands etls_commands_F;
      quit;
      
      %let etls_otherTablesReferToThisTable=0;
      
      %macro etls_CIContents(table=,workTableOut=,inDSOptions=);
         %put NOTE: Building table listing Constraints and Indexes for: &table;
         proc datasets lib=work nolist; delete &workTableOut; quit;
         proc contents data=&table&inDSOptions out2=&workTableOut noprint; run;
         
         data &workTableOut;
            length name $60 type $20 icown idxUnique idxNoMiss $3 recreate $600;
            set &workTableOut;
            type=upcase(type);
            if type eq 'REFERENTIAL' then
            do;
               put "WARNING%QUOTE(:) Target table is referenced by constraints in"
                    " another table: " ref;
               call symput('etls_otherTablesReferToThisTable','1');
               delete;
            end;
            if type='INDEX' and ICOwn eq 'YES' then delete;
         run;
         %rcSet(&syserr); 
         
      %mend etls_CIContents;
      
      %etls_CIContents(table=DEVPRE.TRANS_PRELIM, workTableOut=etls_commands, inDSOptions=);
      
      %if &etls_otherTablesReferToThisTable %then 
         %put WARNING%QUOTE(:) Replacing entire table will fail. Consider an alternate load technique or revising constraints.; 
      %else 
      %do; /* okay - remove foreign keys  */ 
      
         data etls_commands_F; 
            set etls_commands; 
            if upcase(type)="FOREIGN KEY" then 
            do; 
               command='ic delete '||trim(name)||';';
               output etls_commands_F; 
            end; 
         run; 
         
         %put %str(NOTE: Removing foreign keys before dropping table...);
         data _null_;
            set etls_commands_F end=eof;
            if _n_=1 then 
               call execute('proc datasets nolist lib=DEVPRE;modify TRANS_PRELIM;');
            call execute(command);
            if eof then call execute('; quit;');
         run;
         %rcSet(&syserr); 
         
   %end; /* okay - remove foreign keys  */ 
   
   proc datasets lib = work nolist nowarn memtype = (data view);
      delete etls_commands etls_commands_F;
   quit;
   
   /*---- Drop a table  ----*/ 
   %put %str(NOTE: Dropping table ...);
   proc datasets lib = DEVPRE nolist nowarn memtype = (data view);
      delete TRANS_PRELIM;
   quit;
   
   %rcSet(&syserr); 
   
   %let etls_tableExist = 0;
   
%end; /* table exists  */ 

/*---- Create a new table  ----*/ 
%if (&etls_tableExist eq 0) %then 
%do;  /* if table does not exist  */ 

   %put %str(NOTE: Creating table ...);
   
   data DEVPRE.TRANS_PRELIM
           (label = 'abc');
      attrib a length = $60
         format = $60.
         informat = $60.
         label = 'a'; 
      attrib b length = $130
         format = $130.
         informat = $130.
         label = 'b'; 
      attrib c length = $255
         format = $255.
         informat = $255.
         label = 'c'; 
      attrib d length = $135
         format = $135.
         informat = $135.
         label = 'd'; 
      attrib e length = $190
         format = $190.
         informat = $190.
         label = 'e'; 
      attrib f length = $240
         format = $240.
         informat = $240.
         label = 'f'; 
      attrib g length = $10
         format = $10.
         informat = $10.
         label = 'g'; 
      attrib h length = 8
         format = DDMMYY10.
         informat = DDMMYY10.
         label = 'h'; 
      attrib i length = $70
         label = 'i'; 
      attrib j length = $500
         label = 'j'; 
      attrib k length = $20
         label = 'k'; 
      attrib l length = 8
         format = 9.2
         informat = 9.2
         label = 'l'; 
      attrib m length = 8
         format = 9.2
         informat = 9.2
         label = 'm'; 
      attrib n length = $385
         label = 'n'; 
      attrib o length = $600; 
      attrib p length = $130; 
      attrib q length = 8; 
      attrib r length = 8; 
      attrib s length = $350
         label = 's'; 
      attrib t length = $300
         label = 't'; 
      call missing(of _all_);
      stop;
   run;
   
   %rcSet(&syserr); 
   
%end;  /* if table does not exist  */ 

/*---- Append  ----*/ 
%put %str(NOTE: Appending data ...);

proc append base = DEVPRE.TRANS_PRELIM 
   data = &etls_lastTable (&etls_tableOptions)  force ; 
 run; 

%rcSet(&syserr); 

%mend etls_loader;
%etls_loader;

%let etls_recCheckExist = 0; 
%let etls_recnt = 0; 
%macro etls_recordCheck; 
   %let etls_recCheckExist = %eval(%sysfunc(exist(DEVPRE.TRANS_PRELIM, DATA)) or 
         %sysfunc(exist(DEVPRE.TRANS_PRELIM, VIEW))); 
   
   %if (&etls_recCheckExist) %then
   %do;
      %local etls_syntaxcheck; 
      %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); 
      /* Turn off syntaxcheck option to perform following steps  */ 
      options nosyntaxcheck;
      
      proc contents data = DEVPRE.TRANS_PRELIM out = work.etls_contents(keep = nobs) noprint; 
      run; 
      
      data _null_; 
         set work.etls_contents (obs = 1); 
         call symput("etls_recnt", left(put(nobs,32.))); 
      run;
      
      proc datasets lib = work nolist nowarn memtype = (data view);
         delete etls_contents;
      quit;
      
      /* Reset syntaxcheck option to previous setting  */ 
      options &etls_syntaxcheck; 
   %end;
%mend etls_recordCheck;
%etls_recordCheck;

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));
%let etls_recnt=-1;



/**  Step end Clear Table **/

Load style: Replace

New Rows: Append (Proc Append)

Replace: Entire table.

[Patrick · Accepted solution]

@mike73

In your code it says: Load Technique Selection: Replace - EntireTable

I guess what the node label tries to express is that if you change the target metadata table definition then the job will always fully recreate the physical table and though keep the physical table automatically in sync with metadata.

[mike73]

So I learned that if you load a table into itself it just clears the table. Apologies if my description/terminology is incorrect, I'm still relatively new to SAS. I was just confused my the label on the loader, I assumed something was set to cause it to clear itself. Thanks for the feedback.

[LinusH]

So it seems that the name is slightly misleading. I assume that when you execute the job with data in the source the target will be recreated with the source data rows.
Do you have an issue with this job?