Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

How to use a sql statement ina DATA step

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

How to use a sql statement ina DATA step

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?


Accepted Solutions
Solution
‎06-24-2015 02:41 PM
Frequent Contributor
Posts: 130

Re: How to use a sql statement ina DATA step

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;

View solution in original post


All Replies
Solution
‎06-24-2015 02:41 PM
Frequent Contributor
Posts: 130

Re: How to use a sql statement ina DATA step

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;

Super User
Posts: 17,826

Re: How to use a sql statement ina DATA step

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

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

Valued Guide
Posts: 858

Re: How to use a sql statement ina DATA step

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);

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 300 views
  • 0 likes
  • 4 in conversation