BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eagles_dare13
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
dcruik
Lapis Lazuli | Level 10

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

3 REPLIES 3
dcruik
Lapis Lazuli | Level 10

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
Super User

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

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

Steelers_In_DC
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

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