Help using Base SAS procedures

proc sql - how to select variables that are values in another table

Reply
N/A
Posts: 1

proc sql - how to select variables that are values in another table

I have a variable (e.g., VARS) in Table_A that contains a list of variables to be selected from Table_B.

VARS
====
"Age"
"Gender"
"Height"
"Weight"
...

Table B contains variables as named above among many other variables. I would like to select only those variables in VARS from Table B. Here's a code I used:

proc sql;
select (select VARS from Table_A)
from Table_B;
run;

Of course, I got an error message: "ERROR: Subquery evaluated to more than one row."

Is there a way to achieve this in proc sql? I know how to do this by creating %macro variables.

Many thanks,
PROC Star
Posts: 7,492

Re: proc sql - how to select variables that are values in another table

There is probably a more direct way, but the following example should work:


data a;
input a b c d e f;
cards;
1 2 3 4 5 6
7 8 9 10 11 12
;
data b;
input a b c;
cards;
1 2 3
4 5 6
;
proc sql noprint;
select name into :names
separated by ","
from dictionary.columns
where libname eq "WORK" and
memname eq "B"
;

create table want as
select &names.
from A
;
quit;

HTH,
Art
Super Contributor
Posts: 578

Re: proc sql - how to select variables that are values in another table

to follow up on the post...

VARS
"Age"
"Gender"
"Height"
"Weight"

proc sql;
select vars into :VarStmt separated by ', '
from VARS;

select &VarStmt from Table_B;
quit;
Ask a Question
Discussion stats
  • 2 replies
  • 111 views
  • 0 likes
  • 3 in conversation