Not Allowed a Code Node, how do I.....

[paulrockliffe]

Due to some policies I'm working with I've had a project sent back to me because it includes a Code Node.  I need to modify it to remove the Code and I'm struggling!

I have a table that contains a single column containing countries.  I need to be able to add to this list using free-text.  The table is used later as a geographic filter, the countries come from one dataset, but the filter needs to be able to have a bit more flexibility over geographic area, as it's used as a filter for another table that is predominately countries, but also contains some thoer bits that I need to bring into my population.

The code node does nothing more than create a table with a single column containing the additional geographical areas.  These are simply text strings that are found in the later datasets.  I've then appended the two tables together to create a larger table with one column that is then used as the baiss for my filter.

I'm working in Enterprise Guide 4, so may be missing some useful options that are in the later versions.

I have created a parameter to capture the data, though there isn't an option for a parameter that captures the list as a list, the result is "dog cat rabbit mouse etc".  I can access this result in SASHELP.VMACRO, but I don't know how to go about turning the result into a table containing each result on its own line without........ using a Code Node!

Any ideas?

[TomKari]

Well, on the one hand I think you owe me a beer for this one...on the other hand, maybe I owe you a beer for coming up with such an interesting challenge!

I've got two options for you:

Option 1: Simple

But it probably won't fit into your workflow. Instead of having your users enter the text string as a prompt, have them edit a text file, with one string on each line. In your EG project, import the text string. That should work consistently, and be reproducible. By far the simplest.

Option 2: Horribly complicated

Lets assume that your prompt value is "TestString". I've done this as several steps to work out the kinks, but they could be combined.

1. Run a query builder against any dataset (it won't be used, but you can't create a query builder node without an input dataset. I used SASHELP.CLASS). Create a new advanced expression. In the expression box, put

"&TestString."

For the name, call it anything (I'll use TestVar), and in the select data tab, use a summary option of MAX (so that you'll only get one record in the result). Your code should look something like this:

   CREATE TABLE WORK.QUERY_FOR_TEST AS
   SELECT /* TestVar */
            (MAX("&TestString.")) LABEL="TestVar" AS TestVar
      FROM SASHELP.CLASS t1;

So now you've got a SAS dataset with your prompt value in TestVar.

2. Split the values. This part is really yucky. Create a new query builder on your result from step 1. Add as many expressions as the maximum number of parameters you're going to get, each one is named V1, V2, ..., and the expression for each is:

scan(TestVar, 1, " ")

where the 1 becomes 2, 3, etc. to match the variable number. When you run this, you should end up with a one-line dataset with V1, V2, ... containing the individual values.

3. Now it gets easy. Transpose, and drop the rows with blank values.

No code nodes!

   Tom