topic Re: SAS EDI - Strings in the automatically generated where statement in SAS Data Management

SAS EDI - Strings in the automatically generated where statement

CliftonDenning — Tue, 26 Sep 2017 16:50:38 GMT

Using SAS Data Integration studio, when you create a summary transformation in the table options advanced tab you can add a where statement to your code automatically. Unfortunately it adds some code that makes this resolve incorrectly. Putting the following in the where statement text box:

TESTFIELD = "TESTVALUE"

Auto generates:

%let _INPUT_options = %nrquote(WHERE = %(TESTFIELD = %"TESTVALUE%"%));

In the code, used

proc tabulate data = &_INPUT (&_INPUT_options)

But resolves to

WHERE = (TESTFIELD = "TESTVALUE")

_ 22 ERROR: Syntax error while parsing WHERE clause.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, :, INPUT, NOT, PUT, ^, ~.

(Sorry if the spacing doesn't work out in the actual post, the error is under the first double qoutes mark)

My question is this: Is there a way to add a function to the where statement box that would allow this quotation mark to be properly added here?

Note that all functions get the preceding % when added to the where statement automatically and I have no control over that. This seems like something that should be relatively easy to fix but I haven't found a simple way yet.

Re: SAS EDI - Strings in the automatically generated where statement

Patrick — Wed, 27 Sep 2017 10:14:11 GMT

@CliftonDenning

That looks like something worth raising a SAS TechSupport track for.

In my mind the DIS generated code should add an %unquote() function for things to work.

proc tabulate data = &_INPUT (%unquote(&_INPUT_options))

Re: SAS EDI - Strings in the automatically generated where statement

CliftonDenning — Wed, 27 Sep 2017 19:20:24 GMT

Thanks, I'll give that a try and post the results if they respond.

Re: SAS EDI - Strings in the automatically generated where statement

CliftonDenning — Mon, 02 Oct 2017 15:29:15 GMT

Thank you all for your responses. Long story short, I ended up creating a SAS Troubleshooting ticket. The analyst told me that they have now documented the issue, which should now be resolved in a future iteration of DI.

The temporary solution was to create a new transformation, with a slight alteration, adding an UNQOUTE to the source code before the input options:

proc tabulate data = &_INPUT (%unquote(&_INPUT_options)) %unquote(&procOptions);

For those interested you will need to create the transformation in a public subfolder of your project so others can use it. Not what I was hoping for, but a workable solution while waiting for the version update.