Multine Prompt in a Character Column

[rodrichiez]

Hi,

I'm trying to put a multi line prompt in a where clause to filter a character column but I got no result at all and the log doesn't show any error:

7          %LET CIF = 0000005979
8          0000006911
9          0000004955
10         0000007464;
11         
12         ODS _ALL_ CLOSE;
13         OPTIONS DEV=ACTIVEX;
14         GOPTIONS XPIXELS=0 YPIXELS=0;
15         FILENAME EGSR TEMP;
16         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
17             STYLE=HtmlBlue
18             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/6.1/Styles/HtmlBlue.css")
19             NOGTITLE
20             NOGFOOTNOTE
21             GPATH=&sasworklocation
22             ENCODING=UTF8
23             options(rolap="on")
24         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
25         
26         GOPTIONS ACCESSIBLE;
27         
28         %_eg_conditional_dropds(WORK.QUERY_FOR_CLIENTES);
29         


30         PROC SQL;
31            CREATE TABLE WORK.QUERY_FOR_CLIENTES AS
32            SELECT t1.NRO_CUENTA,
33                   t1.CIF,
34                   t1.CLIENTE,
35                   t1.CEDULA,
36                   t1.PASAPORTE,
37                   t1.RNC,
38                   t1.SEGMENTO,
39                   t1.TIPO_CUENTA,
40                   t1.OFICIAL,
41                   t1.CENTRO
42               FROM WORK.CLIENTES t1
43               WHERE %_eg_WhereParam (t1.CIF, CIF, IN, TYPE=S, IS_EXPLICIT=0);
NOTE: Table WORK.QUERY_FOR_CLIENTES created, with 0 rows and 10 columns.

44         QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

[TomKari]

I think I got something similar to your query working. I needed to make two changes:

In the "build a filter" window, I clicked on the arrow beside the values box, and selected &CIF under the Prompts tab. This resulted in a query that looked like this (I was using sashelp.class as my source table).

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_CLASS_0000 AS
SELECT t1.Name,
t1.Sex,
t1.Age,
t1.Height,
t1.Weight
FROM SASHELP.CLASS t1
WHERE t1.Age IN
(
&CIF
);
QUIT;

I added commas between the numeric values, so my prompt value looked like this:

11,
13,
14

Hope this helps!

   Tom

[rodrichiez]

Hi Tom,

The prompt works fine with numeric values, my problem is that I'm filtering in a character column.

[TomKari]

I tried some different variations, but I didn't have any luck. It may take some digging into the macro.

I haven't needed to use this construct before, so I'm not familiar with it.

Tom

[rodrichiez · Accepted solution]

@TomKari Ok, thanks Tom. I have found a solution:

%let customer_id = &CIF;
%let CUST="%sysfunc(tranwrd(&customer_id,%str( ),%str(" ")))";
%put &CUST;

This code add quotes to the values inserted in the prompt.

Regards.