Insert macro variable value into select statement

[abraham1]

Hi all,
From the below table, how to create studyid as macro variable in the demograpy datasets and update all vaue with newly updated study from %let statement.

It is not working

Input data

%let studyid='PAT_213_432';

data dm;
input studyid $ age gender;
cards;
abc_12_21 23 1
bcd_12_29 23 2
abc_11_21 28 1
def_12_21 23 1
;
run;

proc sql;
create table demograpy as
select &studyid, age, gender
from sdtm.dm;
quit;

Expected output

studyid              age gender
PAT_213_432      23 1
PAT_213_432      23 2
PAT_213_432      28 1
PAT_213_432       23 1

[PaigeMiller]

When &studyid is resolved inside of PROC SQL, you get the code

proc sql;
create table demograpy as
select 'PAT_213_432', age, gender
from sdtm.dm;
quit;

and so now I ask you ... is this legal working SAS code? If you typed this in without macro variables exactly as I have typed it above, would it work? What is wrong? Please answer without talking about macro variables: What part(s) of the code would you have to change so that it becomes legal working SAS code??

[Reeza]

I don't think that query makes sense. Your macro variable studyID has a variable value, in the SELECT statement that would be a variable name, not value.

proc sql;
create table demograpy as
select studyID, age, gender
from sdtm.dm
where studyID in (&studyID);
quit;

Personally, I'd name my variables differently to avoid this confusion. (note the removal of the library in this example). I'm assuming your code works and your DM data set is in a library (STDM) but the code posted creates a table DM in the work library instead. 

%let selected_studyid='PAT_213_432';

data dm;
input studyid $ age gender;
cards;
abc_12_21 23 1
bcd_12_29 23 2
abc_11_21 28 1
def_12_21 23 1
;
run;

proc sql;
create table demograpy as
select studyid, age, gender
from dm
where studyid in (selected_studyID);
quit;

[andreas_lds · Accepted solution]

So you want to replace studyid in "dm" with a constant value, i don't think it is a good idea to manipulate the dm, but to create a new dataset:

data want;
  set dm;
  studyid = "&studyid";
run;

[Kurt_Bremser]

You need to give the value a variable name:

proc sql;
create table demograpy as
select &studyid as studyid, age, gender
from sdtm.dm;
quit;

What @andreas_lds proposes is best for your issue.

[abraham1]

Thanks Everyone for the solution and guidance