BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mike73
Calcite | Level 5

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:

Capture.PNG

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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.

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20
Just the picture of the won't tell.
How is that Table Loader configured?
What does the generated SAS code look like?
Data never sleeps
mike73
Calcite | Level 5

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
Opal | Level 21

@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
Calcite | Level 5

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
Tourmaline | Level 20
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?
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1788 views
  • 0 likes
  • 3 in conversation