Currently I'm developing DI job which involves around 40 transformations. During the initial stage of the development, I imported the source data from MS Excel. There are two source files, so did a proc import twice.
Now I'm in the finishing stage of development and at this point I was asked to replace a source file with SQL tables which is already registered in SAS. Data and variable name is same in both excel and SQL table. But length, formats and informats are not same.
If I replace excel files with SQL tables to my job, then almost each transformations throw a warnings for length, formats and informats mismatch. If so, I have to correct the attributes in each and every transformation. Is there any other easy way to change the attributes without touching every transformation? Metadata of the SQL table will not be changed at any time as the table might also be used as source for other jobs.
There is feature in all transformations in DI Studio under mappings tab, after selecting the columns you want to update in the job, then right click and select the option Update Selected source columns ->to End.
Just double check if your are using Append or Table loader transformation. Sometimes the updating works just up to the transformation before them not from that transformations.
So the morale from this is that (in an ideal world) you should know/start with the design of the target table.
Than you should be more resilient towards change in scope down the line.
Of course, a developer is not always responsible for this part, but you could/should definitely ask/expect it from the project.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.