BookmarkSubscribeRSS Feed
qwererty
Calcite | Level 5


Hi,

A few months ago I set up a straightforward process that took two permanent tables (e.g. registered as metadata and stored in library) and used a SQL join transformation.  I then right clicked on the table icon and selected register to create another permanent table with the result of the join.  I would now like to come back and modify the process to put another step in after the join and before loading to premanent dataset. however now when I delete the link and try add in the step (splitter transformation) I am told that "splitter requires a physical table or work table"  Is there a way without rewriting the join transformation that I can modify the existing.

Thanks

Tom

5 REPLIES 5
LinusH
Tourmaline | Level 20

I'm not sitting in front of DIS right now, but if I recall right:

  • disconnect the join from the permanent table
  • right click on the transformation, and chose "Add work table"

As a best practice, it's maintain work tables (views) with the transformations, and use a Table Loader transformation for the target table. It makes the flows easier to adjust 😉

Data never sleeps
qwererty
Calcite | Level 5

I can get the work table back following your instructions, thanks.  My inconvenience now is that all the formual I had in he expression columns have gone.  I assume this is because they were mapped to the permanent metadata object.  but before I replace al 600 of them can you think of a way for me to salvage them using DI studio?

Thanks

Tom

LinusH
Tourmaline | Level 20

600? OMG!

Well, if you expressions are without linebreaks, and you are sure that you have the same sort order of the columns, you could just copy and paste the expressions. A bit risky, but could probably save some work...

Data never sleeps
qwererty
Calcite | Level 5

Unfortunately there are alot of case statements that when I copy else where go into multiple lines.  Mentally preparing myself to do it now.  Seems like a good task for a friday afternoon.  Thanks for the help though I certainly will always use the table loader going forward.

Thanks

LinusH
Tourmaline | Level 20

Just take a deep breath...

For future considerations, doing ETL and DW is far more easy if the data is transposed, leaving fewer columns to maintain in metadata.

Have nice evening! Smiley Happy

Data never sleeps

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
  • 948 views
  • 0 likes
  • 2 in conversation