Thanks all for replying to my question. Great community!! I think, for now, I will call a macro in a User Written transformation and try to do this only with the most risk full external files.   Cheers! Thierry ... View more

Yes, fields to the right are no problem for the import process. It is however a problem when the fields are switched. So the numbers of column "Revenue" are stored at the location where "Number of employees" are stored. Both are numeric so the import proces is fine, technically.   I agree with you that this is a technical solution for something that should be part of good data governance. I will put that on the agenda also, thanks for reminding me this.   Thanks all for your help. For now I think I will skip this step or copy / past code in a User Written transformation. Or create an autocall macro, something like that. ... View more

Thanks for your message Patrick. It does however clean-up the _input macrovariable.   With the custom tranformation I want to check the external file for any changed columns. For instance when an external file has an extra column, I don't want to load the external file. ... View more

I'm not using the OOTB SQL Join Transformation because I need to do more than just the join and it has to be done several times in one job in dozens of jobs. Here is a screen of the situation: Both input sets have the variable name in it. It is the variable I using to join the two sets. But how do I tell SAS to use variable NAME from the upper dataset (class) and not from the lower? Here’s the code SAS generates: %let _INPUT_count = 2; %let _INPUT = work.class; %let _INPUT_connect = ; %let _INPUT_engine = ; %let _INPUT_memtype = DATA; %let _INPUT_options = %nrquote(); %let _INPUT_alter = %nrquote(); %let _INPUT_path = %nrquote(/User Written_A5AYRIWG.C2000GSP%(WorkTable%)); %let _INPUT_type = 1; %let _INPUT_label = %nrquote(); %let _INPUT0 = work.class; %let _INPUT0_connect = ; %let _INPUT0_engine = ; %let _INPUT0_memtype = DATA; %let _INPUT0_options = %nrquote(); %let _INPUT0_alter = %nrquote(); %let _INPUT0_path = %nrquote(/User Written_A5AYRIWG.C2000GSP%(WorkTable%)); %let _INPUT0_type = 1; %let _INPUT0_label = %nrquote(); %let _INPUT1 = work.W47Z4U; %let _INPUT1_connect = ; %let _INPUT1_engine = ; %let _INPUT1_memtype = DATA; %let _INPUT1_options = %nrquote(); %let _INPUT1_alter = %nrquote(); %let _INPUT1_path = %nrquote(/User Written_A5AYRIWG.C2000GSQ%(WorkTable%)); %let _INPUT1_type = 1; %let _INPUT1_label = %nrquote(); %let _OUTPUT_count = 1; %let _OUTPUT = work.W4YW5C; %let _OUTPUT_connect = ; %let _OUTPUT_engine = ; %let _OUTPUT_memtype = DATA; %let _OUTPUT_options = %nrquote(); %let _OUTPUT_alter = %nrquote(); %let _OUTPUT_path = %nrquote(/test_OUTPUT0_A5AYRIWG.C2000GSR%(WorkTable%)); %let _OUTPUT_type = 1; %let _OUTPUT_label = %nrquote(); /* List of target columns to keep */ %let _OUTPUT_keep = name predict; %let _OUTPUT_col_count = 2; %let _OUTPUT_col0_name = name; %let _OUTPUT_col0_table = work.W4YW5C; %let _OUTPUT_col0_length = 8; %let _OUTPUT_col0_type = $; %let _OUTPUT_col0_format = ; %let _OUTPUT_col0_informat = ; %let _OUTPUT_col0_label = %nrquote(); %let _OUTPUT_col0_input0 = name; %let _OUTPUT_col0_exp = ; %let _OUTPUT_col0_input = name; %let _OUTPUT_col0_input_count = 1; %let _OUTPUT_col1_name = predict; %let _OUTPUT_col1_table = work.W4YW5C; %let _OUTPUT_col1_length = 8; %let _OUTPUT_col1_type = ; %let _OUTPUT_col1_format = ; %let _OUTPUT_col1_informat = ; %let _OUTPUT_col1_label = %nrquote(); %let _OUTPUT_col1_input0 = predict; %let _OUTPUT_col1_exp = ; %let _OUTPUT_col1_input = predict; %let _OUTPUT_col1_input_count = 1; %let _OUTPUT0 = work.W4YW5C; %let _OUTPUT0_connect = ; %let _OUTPUT0_engine = ; %let _OUTPUT0_memtype = DATA; %let _OUTPUT0_options = %nrquote(); %let _OUTPUT0_alter = %nrquote(); %let _OUTPUT0_path = %nrquote(/test_OUTPUT0_A5AYRIWG.C2000GSR%(WorkTable%)); %let _OUTPUT0_type = 1; %let _OUTPUT0_label = %nrquote(); /* List of target columns to keep */ %let _OUTPUT0_keep = name predict; %let _OUTPUT0_col_count = 2; %let _OUTPUT0_col0_name = name; %let _OUTPUT0_col0_table = work.W4YW5C; %let _OUTPUT0_col0_length = 8; %let _OUTPUT0_col0_type = $; %let _OUTPUT0_col0_format = ; %let _OUTPUT0_col0_informat = ; %let _OUTPUT0_col0_label = %nrquote(); %let _OUTPUT0_col0_input0 = name; %let _OUTPUT0_col0_exp = ; %let _OUTPUT0_col0_input = name; %let _OUTPUT0_col0_input_count = 1; %let _OUTPUT0_col1_name = predict; %let _OUTPUT0_col1_table = work.W4YW5C; %let _OUTPUT0_col1_length = 8; %let _OUTPUT0_col1_type = ; %let _OUTPUT0_col1_format = ; %let _OUTPUT0_col1_informat = ; %let _OUTPUT0_col1_label = %nrquote(); %let _OUTPUT0_col1_input0 = predict; %let _OUTPUT0_col1_exp = ; %let _OUTPUT0_col1_input = predict; %let _OUTPUT0_col1_input_count = 1; proc datasets lib=work nolist nowarn memtype = (data view); delete W4YW5C; quit; /* List of target columns to keep */ %let _keep = name predict; /* List of target columns to keep */ %let keep = name predict; * ------------------------------------------------------- ; * My code comes here ; * ------------------------------------------------------- ; %rcSet(&syserr); %rcSet(&sysrc); %rcSet(&sqlrc); %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); %let etls_recnt=-1; /** Step end test **/ ... View more

I'm actually looking for the table that is the source for output variable name_1, ds1. Based on the macrovariables for the output variables, I can't find any difference between variable name from ds1 and name from ds2. ... View more

You mean something like this? proc sql noprint ; select 'Name='||strip(name)||' in date='||strip(put(date,date9.)) INTO:footer separated by ' ' from dataset where salary is missing ; quit ; footnote "Salary is missing for &footer." ; proc print data=dataset ; run ; ... View more