topic Re: Can I append multiple tables together into one using SAS DI studio? in SAS Data Management

Can I append multiple tables together into one using SAS DI studio?

ncsthbell — Tue, 15 Dec 2015 18:02:21 GMT

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

Re: Can I append multiple tables together into one using SAS DI studio?

SteveM_UK — Tue, 15 Dec 2015 19:44:55 GMT

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

Re: Can I append multiple tables together into one using SAS DI studio?

LinusH — Wed, 16 Dec 2015 14:43:18 GMT

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! 🙂

Re: Can I append multiple tables together into one using SAS DI studio?

SteveM_UK — Wed, 16 Dec 2015 17:19:53 GMT

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!

Re: Can I append multiple tables together into one using SAS DI studio?

pic_sas — Fri, 18 Dec 2015 11:28:35 GMT

You can use either "Append" transformation which is found under Data group Or use "Set Operator" transformation found under SQL group.

Re: Can I append multiple tables together into one using SAS DI studio?

Patrick — Mon, 21 Dec 2015 08:34:11 GMT

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;