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
... View more
