Unable to Find Template Table to Update Column Length in Data Integration Studio

[David_Luttrell]

Hi,

I'm currently updating a Data Integration Studio Job Built by Consultants, this job uses a custom transform in order to create multiple tables and sheet for ingest. This is definitely the source of the problem as I have aligned all other tables in the job and their column lengths.

This transformation uses a template table in order to create the tables used in the job on the fly. I would be able to update them if they were physically there, however I cannot find the table in which the columns and their parameters are stored.

I really need a second eye to look at this, if anybody could kindly do this for me?

** I know they appear to be in the vtable and vcolumn tables but when I check there they are not present in the tables themselves. This could be something to do with the fact that they're in the work directory. Any suggestions on how to approach this, (breakpointing etc)?

/*==========================================================================* 
 * Step:            99.1.4.008 Import Excel Tab           A5FK4TCA.BN0002G5 * 
 * Transform:       99.1.4.008 Import Excel Tab                             * 
 * Description:                                                             * 
 *                                                                          * 
 * Source Tables:   PERIOD_TAB -                          A5FK4TCA.BB00007X * 
 *                   CAP.&src._PERIOD_&rpt_basis.                           * 
 *                  MISCELLANEOUS_INPUTS_DWH_MAPPING -    A5FK4TCA.BB00008L * 
 *                   ctl.MISCELLANEOUS_INPUTS_DWH_MAPPING                   * 
 * Target Table:    MULTI_EXPENSE_RESERVE -               A5FK4TCA.BB00007B * 
 *                   CAP.EXPENSE_RESERVE_&rpt_basis                         * 
 *==========================================================================*/ 

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

/* Access the data for CAPTURE  */ 
LIBNAME CAP BASE "F:\SAS_Staging\Lev2\capture";
%rcSet(&syslibrc); 

/* Access the data for CONTROL  */ 
LIBNAME ctl BASE "F:\SAS_Utilities\Lev2\control";
%rcSet(&syslibrc); 

%let _INPUT_count = 2; 
%let _INPUT = CAP.&src._PERIOD_&rpt_basis.;
%let _INPUT_connect = ;
%let _INPUT_engine = BASE;
%let _INPUT_memtype = DATA;
%let _INPUT_options = %nrquote();
%let _INPUT_alter = %nrquote();
%let _INPUT_path = %nrquote(/ROOTFOLDER/2. Staging Layer/2.1 Capture/2.1.1 Tables/PERIOD_TAB%(Table%));
%let _INPUT_type = 1;
%let _INPUT_label = %nrquote();

%let _INPUT0 = CAP.&src._PERIOD_&rpt_basis.;
%let _INPUT0_connect = ;
%let _INPUT0_engine = BASE;
%let _INPUT0_memtype = DATA;
%let _INPUT0_options = %nrquote();
%let _INPUT0_alter = %nrquote();
%let _INPUT0_path = %nrquote(/ROOTFOLDER/2. Staging Layer/2.1 Capture/2.1.1 Tables/PERIOD_TAB%(Table%));
%let _INPUT0_type = 1;
%let _INPUT0_label = %nrquote();

%let _INPUT1 = ctl.MISCELLANEOUS_INPUTS_DWH_MAPPING;
%let _INPUT1_connect = ;
%let _INPUT1_engine = BASE;
%let _INPUT1_memtype = DATA;
%let _INPUT1_options = %nrquote();
%let _INPUT1_alter = %nrquote();
%let _INPUT1_path = %nrquote(/ROOTFOLDER/99. Utilities/99.2 Tables/MISCELLANEOUS_INPUTS_DWH_MAPPING%(Table%));
%let _INPUT1_type = 1;
%let _INPUT1_label = %nrquote(This table describes the mapping relationship between tabs from the miscellaneous inputs for all reporting basis, staging tables and data warehouse tables);

%let _OUTPUT_count = 1; 
%let _OUTPUT = CAP.EXPENSE_RESERVE_&rpt_basis;
%let _OUTPUT_connect = ;
%let _OUTPUT_engine = BASE;
%let _OUTPUT_memtype = DATA;
%let _OUTPUT_options = %nrquote();
%let _OUTPUT_alter = %nrquote();
%let _OUTPUT_path = %nrquote(/ROOTFOLDER/2. Staging Layer/2.1 Capture/2.1.1 Tables/MULTI_EXPENSE_RESERVE%(Table%));
%let _OUTPUT_type = 1;
%let _OUTPUT_label = %nrquote();
/* List of target columns to keep  */ 
%let _OUTPUT_keep = Shock Label Label2 ProductGroup Currency Bal_Sheet_tag Number PERIOD;

%let _OUTPUT0 = CAP.EXPENSE_RESERVE_&rpt_basis;
%let _OUTPUT0_connect = ;
%let _OUTPUT0_engine = BASE;
%let _OUTPUT0_memtype = DATA;
%let _OUTPUT0_options = %nrquote();
%let _OUTPUT0_alter = %nrquote();
%let _OUTPUT0_path = %nrquote(/ROOTFOLDER/2. Staging Layer/2.1 Capture/2.1.1 Tables/MULTI_EXPENSE_RESERVE%(Table%));
%let _OUTPUT0_type = 1;
%let _OUTPUT0_label = %nrquote();
/* List of target columns to keep  */ 
%let _OUTPUT0_keep = Shock Label Label2 ProductGroup Currency Bal_Sheet_tag Number PERIOD;


%let src_file = %nrquote(&landing_act.\&env.\miscellaneous_inputs\Miscellaneous_inputs_&rpt_basis..xlsx);
%let template_table = %nrquote(&_OUTPUT0.);
%let misc_tabs_dwh_mapping = %nrquote(&_INPUT1.);
%let period_tab = %nrquote(&_INPUT0.);
%let exception_cols = ;

/* List of target columns to keep  */ 
%let _keep = Shock Label Label2 ProductGroup Currency Bal_Sheet_tag Number PERIOD;
/* List of target columns to keep  */ 
%let keep = Shock Label Label2 ProductGroup Currency Bal_Sheet_tag Number PERIOD;

%let rpt_basis=%upcase(&rpt_basis.);

/* Get PERIOD value for current Excel file */
data _null_;
	set &period_tab.;
	call symputx('capture_period',period);
run;

/* Get name of the tab for current reporting basis mapped to target DWH table */
proc sql noprint;
	select &rpt_basis.
	into :tab_name trimmed
	from &misc_tabs_dwh_mapping.
	where upcase(staging_table)=tranwrd(upcase(scan("&template_table.", 2, '.')), "&rpt_basis.", '&RPT_BASIS.');
;
quit;

%put capture_period=&capture_period.;
%put tab_name=&tab_name.;

/* Get names of columns from specified template(capture) table */
proc sql noprint;
    create table work.col_names as
    select name 
    from sashelp.vcolumn
    where upcase(libname) = upcase(scan("&template_table.", 1, '.'))  
    and upcase(memname) = upcase(scan("&template_table.", 2, '.'));
quit;

/* Build text string for KEEP= data set option and assign it to a macro var */
data work.keep_cols;
    set work.col_names;
    length keep_cols $1000;
    retain keep_cols '';
    keep_cols = catx(' ', keep_cols, name);
    call symputx('keep_cols', keep_cols);
run;
 
%put keep_cols=&keep_cols.;

/* Import specified tab in specified Excel file to a work table */
%import_excel_to_sas(src_table=&template_table.,
					 		tgt_table=work.result_table,
							src_file=&src_file.,
				         sheet_name=&tab_name.,
							exception_cols=&exception_cols.);

/* Get number of variables in the imported work table */
proc sql noprint; 
  select nvar into :nvar 
    from sashelp.vTABLE
      where upcase(libname) = "WORK" and upcase(memname)="RESULT_TABLE";
quit;
 
%put nvar=&nvar.;
 
data &template_table. (keep=&keep_cols. PERIOD);
	set work.result_table;
	length PERIOD $7;
   /* Remove obs that contains null values for all variables */
   if cmiss(of _all_) ^= sum(&nvar., 1);
	PERIOD = upcase("&capture_period.");
run;


				         

%rcSet(&syserr); 
%rcSet(&sysrc); 
%rcSet(&sqlrc); 



/**  Step end 99.1.4.008 Import Excel Tab **/

Kind Regards,

David

[Patrick]

Is this code working without throwing an error?

%let _OUTPUT0 = CAP.EXPENSE_RESERVE_&rpt_basis;
......
%let template_table = %nrquote(&_OUTPUT0.);

.....

proc sql noprint;
    create table work.col_names as
    select name 
    from sashelp.vcolumn
    where upcase(libname) = upcase(scan("&template_table.", 1, '.'))  
    and upcase(memname) = upcase(scan("&template_table.", 2, '.'));
quit;

.....

data &template_table. (keep=&keep_cols. PERIOD);
	set work.result_table;
	length PERIOD $7;
   /* Remove obs that contains null values for all variables */
   if cmiss(of _all_) ^= sum(&nvar., 1);
	PERIOD = upcase("&capture_period.");
run;

From what I can see in the code the template table is "output" but then that's referenced in the SQL before it gets created. So in case there isn't a previous node which creates "CAP.EXPENSE_RESERVE_&rpt_basis" things can't work.

As a comment (and I can be wrong): 

Is this is really a custom transformation or is it a "user written code" node? If it's a custom transformation then it looks to me as this has been more of a "copy/paste" excercise using static generated code and not how a custom transformation should look like. 

The following bit of code looks to me as the initial developer never heard of SAS SQL "select into :macrovar".... and though done it in two steps.

/* Get names of columns from specified template(capture) table */
proc sql noprint;
    create table work.col_names as
    select name 
    from sashelp.vcolumn
    where upcase(libname) = upcase(scan("&template_table.", 1, '.'))  
    and upcase(memname) = upcase(scan("&template_table.", 2, '.'));
quit;

/* Build text string for KEEP= data set option and assign it to a macro var */
data work.keep_cols;
    set work.col_names;
    length keep_cols $1000;
    retain keep_cols '';
    keep_cols = catx(' ', keep_cols, name);
    call symputx('keep_cols', keep_cols);
run;
 
%put keep_cols=&keep_cols.;

 It's then also strange how and at what time &keep_cols get used and without digging into the last detail I start to feel that this code only works as intended by the developer once it has been run more than once in the same DIS session (so WORK tables already exist from a previous run).

If I would be you I'd analyze what this code snippet really should be doing and then discard it and implement something clean (...and yes, I know, that's not always possible in real live situations).