0

i need to use a append object after a series of join that have a conditional run... So the join step may be not execute if the condition is not verified and his work physical dataset will not be created.

The problem is that the append step take an error if one o more input physical dataset are not created.

Is there a smart way to create a physical empty table from a metadata structure of the works table of the joins or to use the append with some non-created datasets?

The create table with the list of all field is not a real solution because i've to replicate it per 8 different joins and then replicate the job 10 times...

Thanks to all Roberto

Roberto
  • 1
  • 2

2 Answers2

1

Thank you for your comments.

What you should do:

  1. Amend your conditional node so that it would on positive condition to create a global macro variable with value of MAX. On negative condition to create the same variable with value of 0.
  2. Replace offending SQL step with "CREATE TABLE" node
  3. In the options for "CREATE TABLE", specify macro variable for "MAXIMUM OUTPUT ROWS (OUTOBS)". See the picture below for example of those options.

So now when your condition is not met, you will always end up with an empty table. When condition is met, the step executes normally.

I must say my version of DI Studio is a bit old. In my version SQL node doens't allow passing macro variables to SQL options, only integers can be typed in. Check if your version allows it because if it does, then you can amend existing SQL step and avoid replacing it with another node.

One more thing, you will get a warning when OUTOBS options is less then the resulting would be dataset.

Let me know if you have any questions.

See the picture for create table options: enter image description here

Vasilij Nevlev
  • 1,449
  • 9
  • 22
  • this is the diagram http://s32.postimg.org/8srd9sf7p/shot.png. The empty table is not created because the join step don't run if the condition is not verified – Roberto May 18 '16 at 10:12
  • the conditional run check a macro variable populated with a flag value derivated from a query that indicated if the job have to run or not. The apped is done by the specific object in sas DI but in the code is doing a data with many set – Roberto May 18 '16 at 10:44
  • @Roberto I have amended my answer. See if it works for you. – Vasilij Nevlev May 18 '16 at 13:51
  • I've try your solution but the result is the same... if the condition is not verified the create table don't run and the work table will not be created – Roberto May 19 '16 at 07:47
  • @Roberto Sorry about that. What sort of condition is in the condition block? – Vasilij Nevlev May 19 '16 at 08:10
  • in the "pre code" of the condition block is inizialized a macro variable in Y or N with a query. The in the condition tab there's the check if the variabile il Y for run. So i set outobs macro variable with MAX – Roberto May 19 '16 at 08:33
  • @Roberto here is an idea. If you copy the precode from the condition block to the create table node. Remove the condition block and try again. I think that should work because "create table" will always run, but the table won't get populated if condition is negative. – Vasilij Nevlev May 19 '16 at 12:33
  • The problem is that i can't allow to run the step that is not necessary because the run time increse significantly also if the join not return data. At the end i've created another step that extract 0 row from the source table by the condition 1=0 in the where tab. In this way i have a empty table that i can use with a data/set in the post sql of the conditional run if the work table of the join does not exist. – Roberto May 23 '16 at 10:32
0

At the end i've created another step that extract 0 row from the source table by the condition 1=0 in the where tab. In this way i have a empty table that i can use with a data/set in the post sql of the conditional run if the work table of the join does not exist.

This is not a solution but a valid work around.

Roberto
  • 1
  • 2