How to get this prompt working ?

[GuiVtzl]

I did this below. What I'd like is to create a table where the prompt (TEXT) gets for values a where statement. Values are WHERE PERFORMING_FLAG = "Yes" , it display "Yes" to the user, and WHERE PERFORMING_FLAG = "No" that displays "No" to the user, and /* */ which displays "All"

So I wrote the code below, and it doesn't work.

What I want is being able To make the user choose between two options that are WHERE statement and one option that is nothing (so no WHERE statement).

How should I do my prompt or correct the code ? The prompt is &PERFORMING_FLAG

Or I guess there is a possibility to make it more clear, could be you please enlighten me ?

PROC SQL;

CREATE TABLE TRAVAIL.Q113_INPUTS AS

   SELECT t1.ID_INTERNATIONAL,

          t1.NOM_TIERS,

          t1.CD_COTATION,

          t1.'Lb Secteur EIS GB'n,

          t1.'Zone Resp'n,

          t1.ID_FAC,

          t1.LB_ENGAGEMENT_GB,

          t1.'Type Encours'n,

          t1.'Type Encours GB'n,

          t1.IND_BILAN_HORS_BILAN,

          t1.DT_AUTO,

          t1.DT_ECH,

          t1.'Devise Final1'n,

          t1.'Devise Final'n,

          t1.SumOfMT_UTIL_EUR,

          t1.SumOfMT_AUTO_EUR,

          t1.PNU_conf,

          t1.'Regulatory_RWA'n,

          t1.SumOfMT_EXPO_IRBA,

          t1.TX_CCF_IRBA,

          t1.SumOfX_EDF1_IRBA,

          t1.SumOfX_GRR_IRBA,

          t1.SumOfX_MAT_IRBA,

          t1.PERFORMING_FLAG,

          t1.IND_CONF,

          t1.'Business Country'n,

          t1.'LB Pays Resp'n,

          t1.'Lib Metier 25'n,

          t1.'Lib act agg 25'n,

          t1.LB_GRP_AFFAIRE,

          t1.RATING_GROUPE,

          t1.C3_CLIENT_LINE,

          t1.CD_GRP_AFFAIRE,

          t1.Credit_Type_Profiling,

          t1.CD_TP_CTRP_CAPRI,

          t1.LIB_METIER_BFI,

          t1.FORTIS_FLAG,

          t1.CD_ENGAGEMENT,

          t1.LB_ENGAGEMENT

FROM TRAVAIL.Q113_INPUTS_1 t1

       &PERFORMING_FLAG;

QUIT;

[Haikuo]

Although more information is needed, but for a quick fix based on my guessing,  try this:

Change:

FROM TRAVAIL.Q113_INPUTS_1 t1

  &PERFORMING_FLAG;

QUIT;

To:

FROM TRAVAIL.Q113_INPUTS_1 t1

where   PERFORMING_FLAG = &PERFORMING_FLAG;

QUIT;

If that doesn't help, then your error log will be needed, or you need to let us know "it doesn't work" HOW.

Haikuo

[GuiVtzl]

Thank you for your help Hai.kuo

In fact, what you guess as a quick fix is what I wrote in first place. But my problem here is that the prompt only replace the value in the WHERE statement.

That would be useful, but one of the choices for the user in the prompt is to select "All" (i.e. No WHERE statement). This way, when the user selects "All", the line becomes " WHERE PERFORMING_FLAG=   " and then it gets an error because SAS is expecting something.

This is what is tricky for me here, my prompt should not only change the value inside the WHERE statement, it should also give the possibility to remove it.

Anyway, I show you below my error log, hoping it will help you (helping me ) :

	t1.LB_ENGAGEMENT
57	FROM TRAVAIL.Q113_INPUTS_1 t1
58		&PERFORMING_FLAG.;
	_
	22
	200

ERROR 22-322: Syntax error, expecting one of the following: un nom, une chaîne entre guillemets,

	une constante numérique, une constante date temps, une valeur manquante, (, *, +, -,
	ALL, ANY, BTRIM, CALCULATED, CASE, INPUT, PUT, SELECT, SOME, SUBSTRING, TRANSLATE,
	USER.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

59

QUIT;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time

0.10 secondes

[Amir]

Hi,

I have responded to your double post https://communities.sas.com/message/170502#170502

Seeing the diagnostics you have shown here I would suggest we still need to see the SAS code that is creating macro variable PERFORMING_FLAG.

If for some reason you cannot change the creation of PERFORMING_FLAG, then try make the value you use something like (for example):

"" or 1

so that PERFORMING_FLAG resolves to:

WHERE PERFORMING_FLAG="" or 1

which should pick up all records. But it would be better to not generate the where clause at all, so we would need to see the SAS code that is creating PERFORMING_FLAG for that.

Regards,

Amir.

[GuiVtzl]

Thank you Amir,

the macro variable PERFORMING_FLAG is created via a prompt (See the image below).

I try to put "" or 1, but the table created is empty (SAS doesn't find any 1 or "" values in the colum PERFORMING_FLAG)

[GuiVtzl]

Thank you all for your help, your remarks made me think again and again (even in my sleep) about this problem.

I've just come up with a solution that actually works. But I have one last question, and it is What do you think about my solution ? Is it robust ? Or just a kind of luck ?

So I changed my code into WHERE PERFORMING_FLAG="&PERFORMING_FLAG" with three choices :

Yes

No

_%

[Amir]

Hi,

I don't have access to EG, so might be missing something here, but I would have thought:

WHERE PERFORMING_FLAG="_%"

would look for an underscore followed by a percent sign, those wildcard characters would normally be used with the like operator, e.g.:

WHERE PERFORMING_FLAG LIKE "_%"

and if this is what you have then I would suggest reducing it further to:

WHERE PERFORMING_FLAG LIKE "%"

But if what you have done works using the equality operator, then perhaps it is for someone else who has access to EG to comment on it.

Regards,

Amir.

[Haikuo]

Hi @GuiVtzl,

First I 'd like to congratulate your success of solving your own problem, while I can't repeat your luck using "_%" with "=" operator in STP (Stored Process). Are your sure you used "=" instead of 'LIKE" as suggested?

One way going this around if your code is in the format of :  WHERE PERFORMING_FLAG = "&PERFORMING_FLAG" is to use query builder and check "All possible values" selection.

Unfortunately it does not work on STP out of box, you need to get it to work through query builder, then convert it to STP, so it may not as flexible as something working straightly on the SAS code in STP, although in your case, query builder seems to be adequate.

OK, now is the fun part. If going back to your original code:

FROM TRAVAIL.Q113_INPUTS_1 t1

  &PERFORMING_FLAG;

It came to me that STP  has some 'hidden' rules in term of Macro quoting. Notice the code you have for Prompt:

where  PERFORMING_FLAG = "Yes"

And the error message translated into English : (BTW, I didn't do the translation, rather, I tried to repeat your problem in a hypothetical setting)

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, ALL, ANY, BTRIM, CALCULATED, CASE,

              INPUT, PUT, SELECT, SOME, SUBSTRING, TRANSLATE, USER. 

ERROR 200-322: The symbol is not recognized and will be ignored.

The SAS code is correct in plain SAS code or in the SAS code wrapped with Macro definition, however it does not work in STP, and the reason is hinted in the error message: expecting one of the following: a name, a quoted string.  WTH? SAS is complaining the lack of a quoted string while We do have one "Yes"!!! So there is something fishy here, but we have to play along. SAS complaining lack of a quoted string doesn't there isn't one, it could mean it has been quoted out, so SAS doesn't see it. So I tried this:

FROM TRAVAIL.Q113_INPUTS_1 t1

  %unquote(&PERFORMING_FLAG);

And guess what, It worked!

Here is the definition of my prompt:

Please notice that the third one: where 1, which means always true, but I have to put it out there as it does not take blanks.

I am in the process of learning STP, so I am really glad you have raised this question, it helped me deepen my understanding how STP works.

Haikuo