BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ncsthbell
Quartz | Level 8

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
pic_sas
SAS Employee

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

5 REPLIES 5
SteveM_UK
Obsidian | Level 7

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

LinusH
Tourmaline | Level 20

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
SteveM_UK
Obsidian | Level 7

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!

pic_sas
SAS Employee

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

Patrick
Opal | Level 21

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 5 replies
  • 6692 views
  • 3 likes
  • 5 in conversation