I have 6 different tables that have common columns, however based on the data source, there 'are additional columns for each 'source type' that are named different and not common in all the tables. I am looking for a way I can create one table from the 6 sources, basically an append.
The output table would have all the common column names' and all the 'variable column names' from the 6 tables as the output table definition. Not sure the best tool to use for this. I have never used SAS DI so don't know a lot about it.
I welcome any suggestions!
Thanks
You can use either "Append" transformation which is found under Data group Or use "Set Operator" transformation found under SQL group.
Hi
Register each of your input tables, create a job and drag an 'Append' transform onto it ('Append' is under the 'Data' transforms), then drag each of your tables on as well. Connect each of the inputs to the Append by using a mouse drag and if you open its Properties and look at the Mappings tab you should find the output table has automatically added and mapped the input columns. Note. if the lengths differ for some 'common' columns then you may get warnings to resolve that.
Then right click on the output of the Append and use 'Register Table' to define your output.
The result should be a job that does what you're asking. Run it to see the results, or look at the code if you're curious to see how it works.
If you're new to DI Studio I recommend browsing the online documentation (from Help on the DI Studio menu bar) and having a look at the section on Transforms, it has some well-explained simple examples there.
Regards
Steve
Nice tutorial @SteveM_UK 🙂
But my warning bells alarms when I hear "new to DI", and since you ask the forum of such a task, there's seem to be none at you site that can get you started?
DI Studio can seem simple with it's GUI, point-and, drag-and-drop etc.
But it's all about structure. You can really make a mess in DI as well, and the you don't the benefits of the investment om the ETL tool.
Each site should have the data architecture documented. And en ETL development lead/specialist should have set some basic development rules/best practices. This to insure to consistence and maintainability of the solution.
But anyway, good luck! 🙂
Good point @LinusH - there's a lot more to building a good data management environment than point-and-click job construction.
As you say, planning and structuring the environnment is important; so too are standards: use of check-out/check-in, comments and quicknotes, naming and label conventions, standards for error detection etc - you're right to identify such things as essential and part of what an experienced ETL lead specialist should be doing.
Still, newcomers have to start somewhere, and learning what DI Studio can do for you is a useful first step.
Good luck from me too!
You can use either "Append" transformation which is found under Data group Or use "Set Operator" transformation found under SQL group.
Only use DIS if you plan to implement production worthy ETL processes. If this is not the case then just use something like SAS Studio or SAS EG.
With standard SAS code:
data All_of_it;
set table_1 table_2 ....;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.