SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

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

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

 

 


Accepted Solutions
Solution
‎01-04-2016 04:42 PM
SAS Employee
Posts: 4

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

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

View solution in original post


All Replies
Occasional Contributor
Posts: 11

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

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

Super User
Posts: 5,260

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

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! :-)

Data never sleeps
Occasional Contributor
Posts: 11

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

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!

Solution
‎01-04-2016 04:42 PM
SAS Employee
Posts: 4

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

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

Respected Advisor
Posts: 3,908

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

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;

 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 523 views
  • 2 likes
  • 5 in conversation