BookmarkSubscribeRSS Feed
choi
Calcite | Level 5
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,
2 REPLIES 2
art297
Opal | Level 21
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
DBailey
Lapis Lazuli | Level 10
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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 574 views
  • 0 likes
  • 3 in conversation