topic Re: How to use a sql statement ina DATA step in SAS Data Science

How to use a sql statement ina DATA step

eagles_dare13 — Wed, 24 Jun 2015 18:32:43 GMT

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?

Re: How to use a sql statement ina DATA step

dcruik — Wed, 24 Jun 2015 18:41:42 GMT

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;

Re: How to use a sql statement ina DATA step

Reeza — Wed, 24 Jun 2015 19:01:58 GMT

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

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

Re: How to use a sql statement ina DATA step

Steelers_In_DC — Wed, 24 Jun 2015 19:07:39 GMT

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;

;

run;

proc sql;

create table want as

select a,5 as B

from test

where exists (select lookup

from look_up);