I leave up to others especially SAS to figure out why it took longer.
Here is my summary of the code generator and it may not totally correct. I only spent about 20 minutes looking at the faster log.
Your data seems very small to me, no reason for 12hrs.
Th SAS code generators is relying
on sql views and in your case it looks like 80%
of the views are not needed. The generator does not
understand when to use a datastep.
I could be wrong but It looks like the last five long sql left join views can be combined into one
sql procedure. It looks like you are using left joins to add fields.
You should be able to optimize a single proc sql with multiple left joins.
Because of all the unnecessary code you are asking a lot
from the SAS interpreter. Perhaps SAS enhancements with 9.4 are the issue.
These code snippets very strange.
1045 proc sql;
1046 create table work.etls_mapped as
1047 select
1048 CFCIF_2
1049 label = 'CFCIF#',
1050 CFASEQ,
1051 CFAPTY,
1052 CF2DT2,
1053 CF2PRV,
1054 CFSTSN
1055 from work.W5OB6DH5
1056 ;
seems circular
1061 data work.W5OB6DH5;
1062 set work.etls_mapped;
1063 run;
Generally this is not a good programming practice.
1248 data
1249 work.W5O7ZDVI
1250 work.W5O7ZDVS
1251 work.W5OB570T ;
1252 set &SYSLAST;
1253 output work.W5O7ZDVI;
1254 output work.W5O7ZDVS;
1255 output work.W5OB570T;
1256 run;
Multiple of these (substitute a keep and where)
replace with
from or set &syslast(keep cp.. where=(trim...='4'))
1343 proc sql;
1344 create view work.W5O7ZH2B as
1345 select
1346 CP3UCD
1347 label = 'CP3UCD',
1348 CP3DSC
1349 label = 'CP3DSC',
1350 cp3rid,
1351 cp3uic,
1352 cp3uid
1353 from &SYSLAST
1354 where trim(left(cp3uic)) = '4'
1355 ;
Auto code generation is a great marketing tactic.
Code generators do not comment code?
... View more
