topic Re: Insert macro variable value into select statement in SAS Programming

Insert macro variable value into select statement

abraham1 — Tue, 19 Jul 2022 19:06:53 GMT

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

Re: Insert macro variable value into select statement

PaigeMiller — Tue, 19 Jul 2022 19:40:20 GMT

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??

Re: Insert macro variable value into select statement

Reeza — Tue, 19 Jul 2022 19:40:12 GMT

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;

Re: Insert macro variable value into select statement

andreas_lds — Wed, 20 Jul 2022 04:51:02 GMT

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;

Re: Insert macro variable value into select statement

Kurt_Bremser — Wed, 20 Jul 2022 06:38:23 GMT

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.

Re: Insert macro variable value into select statement

abraham1 — Wed, 20 Jul 2022 17:32:03 GMT

Thanks Everyone for the solution and guidance