topic How to make this prompt work ? in SAS Programming

How to make this prompt work ?

GuiVtzl — Mon, 10 Jun 2013 20:32:24 GMT

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;

Re: How to make this prompt work ?

UrvishShah — Tue, 11 Jun 2013 11:41:17 GMT

Can you please share how you have created the Macro Variable &PERFORMING_FLAG. ???

If you want WHERE statement as a value of Macro Variable to be solved in Proc Sql, then you need to quote the value like this...

%let PERFORMING_FLAG_YES = %str(where performing_flag = "yes");

%let PERFORMING_FLAG_NO = %str(where performing_flag ; "No");

A part from that, also check for case sesitivity in your data...Like How "Yes" and "No" are coded...Whether it is propcase,upcase or lowcase...Accordingly store the values in Macro Variables...

Hope it helps

-Urvish

Re: How to make this prompt work ?

Amir — Tue, 11 Jun 2013 11:41:35 GMT

Hi,

If I've understood you correctly, you already know how to set up macro variable PERFORMING_FLAG as either:

WHERE PERFORMING_FLAG = "Yes"

WHERE PERFORMING_FLAG = "No"

but you would also like to set it up to pick up all records. This can be done by setting it, for example, to:

WHERE 1

which will be true for all records, or just set the macro variable PERFORMING_FLAG to blank so that no where clause is applied.

If this does not fit with what you are trying to do then please explain further, also showing what code you are using to create macro variable PERFORMING_FLAG.

Regards,

Amir.