Reading in a List from a prompt

[mruane]

Hi

Fairly new to SAS. Im currently working on a project where i need to read in multiple int values from the prompt. When I run the program, It only reads the first value as the number even though I am trying to read in all of the values from the prompt.  Any ideas on how I can get more than one account number in my query to show?

FROM ATS_ORDERS t1
           LEFT JOIN ATS_CUSTOMERS t4 ON (t4.ID = t1.BILL_TO_CUST_ID)
           LEFT JOIN ATS_ORDER_ITEMS t3 ON (t1.ODR_NUMBER = t3.ODR_ODR_NUMBER)
           LEFT JOIN ATS_ADDRESSES t2 ON (t1.ODR_NUMBER = t2.ODR_ODR_NUMBER)
      WHERE t1.AGREED_DELIVERY_DATETIME >= trunc(sysdate-14) AND t1.AGREED_DELIVERY_DATETIME <= trunc(sysdate+14)
           AND  t4.Account_Number = &ACCT_NUMBER

[TomKari]

Your SQL syntax will only accept one number. If you want to select more, you'll need to use an IN clause as in the following. But first, you'll need to get multiple values into your macro variable.

Tom

proc sql noprint;
	create table NewTable as
		select * from SASHelp.Class
			where Age in(12, 14);
quit;

[ballardw]

It may depend on whether your t4.Account_Number variable is numeric or character and exactly what the &ACCT_NUMBER prompt holds.

Likely you want an IN comparison which will return a "true" result if the variable has any of a list of values.

AND  t4.Account_Number  IN ( &ACCT_NUMBER)

If your account_number values are actually character and the prompt a list of numbers (no quote marks around values) or vice versa you will get an error of different data types.

So you may have to show some example values for likely fixes to the simple code.

[mruane]

The prompt is set up as numeric. I tried to set up a macro variable to reference but that is not working either.

data _null_;
	length int $32767 ;
	do i=1 to &ACCT_NUMBER_Count;
		int = catx(' ',(cats('&ACCT_NUMBER', i)));
		end;
		call symputx('ACCT_NUMBER_ALL',int);

I am now referencing it here in the where clause but it only resolves to a singular input value not the multiple from the prompt

WHERE t4.Account_Number IN (&ACCT_NUMBER_ALL)

[TomKari]

Now we need to know how you defined your prompt, as that determines how the macro variable(s) are created. Take a picture of the "Prompt Type and Values" tab in the "Add New Prompt" dialogue, and post it. It's the screen that looks like this:

[Tom]

Do EG prompts work like stored process prompts?  When you allow multiple selects it creates multiple macro variables. One with the count and then one for each value with a numeric suffix on the name.

Doesn't EG guide come with a macro to automatically generate what you want?  %eg_where() or something like that?

[mruane]

Yes there is, but currently the program has a connection to oracle. The code is written with oracle syntax. I cant reference %eg_where()

with this connection.

[TomKari]

The Oracle syntax shouldn't matter. There's only two pieces involved in using the prompt.

1. Setting up the prompt definition. That will define what macro variable(s) will be created. That's why I need you to post the screen shot of your definition.

2. In the query, it will need to be informed that prompt variables will be used, and the correct formatting of the macro variable will need to be used in the query.

Once you've provided (1), it should be possible to figure it out.

Tom