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.
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.
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.
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.
Thank you TonyL for the quick response.
This is what I have
But for some reason only one group gets pulled in.
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.
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.
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.
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!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.