How to use a sql statement ina DATA step

[eagles_dare13]

How to achieve this in SAS?

DATA TEST;

A=1;

B=2;

IF A IN (LIST_OF_VALUES) THEN B = 5;

OUTPUT;

RUN;

LIST_OF_VALUES is a column in a lookup table.

Ideally, Iwould like something like:

DATA TEST;

A=1;

B=2;

IF A IN (select LIST_OF_VALUES from TABLE_TEST) THEN B = 5;

OUTPUT;

RUN;

But this syntax is not allowed.

How to achieve this?

[dcruik · Accepted solution]

You can use the SQL procedure to create a macro array to call and use as a list of values in the data step.  It's not in one step, but I believe it would do what you're looking for.

Hope this helps!

proc sql noprint;

select variable into: LIST_OF_VALUES separated by ", "

from TABLE_TEST;

quit;

data TEST;

A=1;

B=2;

If A in (&LIST_OF_VALUES) then B=5;

output;

run;

[Reeza]

I recommend proc format, and find this paper well written.

http://www2.sas.com/proceedings/sugi30/001-30.pdf

[Steelers_In_DC]

The first thing that came to mind is dcruik solution but here's another way.  Depending on what you have an need this might work:

DATA TEST;

A=1;

B=2;

output;

RUN;

data look_up;

input lookup;

cards;

1

2

3

4

5

;

run;

proc sql;

create table want as

select a,5 as B

from test

where exists (select lookup

          from look_up);