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

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

choi — Fri, 25 Mar 2011 21:38:57 GMT

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,

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

art297 — Fri, 25 Mar 2011 23:15:19 GMT

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

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

DBailey — Sat, 26 Mar 2011 12:36:50 GMT

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;