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 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.
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
Hi Tom,
The prompt works fine with numeric values, my problem is that I'm filtering in a character column.
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
@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.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.