topic Re: DataFlux Work Table in SAS Data Management

DataFlux Work Table

beamer108 — Mon, 01 Mar 2021 16:00:40 GMT

I am new to using work tables. I have a process job that contains a Process Flow Work Table Reader node that contains the 'group number' information that I want to pull data on. My next step joins to a data job that references the work table reader and joins to a sql node that pulls from a different data connection. When I run the dj it only pulls in one group id, the other group numbers aren't considered. So I thought that if I added an 'if then' node immediately after the 'process flow work table reader' that said if its the last row - then stop, otherwise pull in the next group id and run against the dj.

Unfortunately I still only get one group row from the work table pulling in to get details on. Any suggestions? Thanks in advance.

Re: DataFlux Work Table

TonyL_ — Mon, 01 Mar 2021 17:49:02 GMT

Sounds like you are on the right track. In this attached screenshot, I'm showing a loop that reads the Work Table row by row. This is how you would set it up, making sure in the Workflow reader ("Read from Temp Table" in my pic) that you set the Source Binding to point to your Work Table on the "Table" entry, as well as the "Default Value" of Readmode set to "NEXTROW" as indicated on the Inputs tab.

I think this should get you closer to getting it to run. It will iterate through this loop for every row on your work table.

Re: DataFlux Work Table

TonyL_ — Mon, 01 Mar 2021 17:53:58 GMT

Also, in the conditional node pictured (End of Work Table in my screenshot) you'll need to set up the Expression entry and the EOF entry as pictured below. The Source Binding for EOF here needs to point to the Workflow Table Reader _EOF.

Re: DataFlux Work Table

beamer108 — Mon, 01 Mar 2021 18:10:08 GMT

Thank you TonyL for the quick response.

This is what I have

But for some reason only one group gets pulled in.

Re: DataFlux Work Table

TonyL_ — Mon, 01 Mar 2021 18:22:22 GMT

I notice that you have eof on the left in lower case, however in the equation it shows as EOF==1. I'd probably redo that one on the left to upper case to match the equation, for starters.

Re: DataFlux Work Table

beamer108 — Mon, 01 Mar 2021 18:23:33 GMT

when I pull up the adv prop for the sql node (within the dj) after running this I get:

In the default value SQL_STMT Default value it shows my macro embedded in my sql node as this:

but when I at the 'run time value' I show this:

and grp_subgrp_dim.grp_id = '00012345' - which is one of the group numbers in my work table, and the one group that appears in my output txt file.

Re: DataFlux Work Table - FIXED

beamer108 — Wed, 03 Mar 2021 14:46:50 GMT

I found in the adv prop of my text file output node that the default setting for 'Append' is set to false. Once I updated this to 'true' the job ran successfully pulling in all rows from my work table.

Thanks for the assistance.

Re: DataFlux Work Table - FIXED

TonyL_ — Wed, 03 Mar 2021 15:53:31 GMT

OK, glad to hear that. So it looks like it was actually looping over the work table correctly, but probably just overwriting your results each time around. Good catch!