concatenating variable names

[soham_sas]

Hi

i have a data set which contains some value (lets say a,b,c e.t.c) , now i have to create a macro for those values and that macro i have to use in a sql like (where name in (&macro)

so i am trying to concatenate the observations by single cotes and a comma but its not working

for example i am trying the sashelp.class

HAVE	WANT
F	'F',
M	'M'

also please note that in the last observation we don't need the comma to be there in the result

proc sql;
create table test1 as
select distinct sex from sashelp.classfit
order by sex;
quit;

data test2;
set test1;
by sex;
if last.sex = 1 then
sex1=cat("'",sex,"'"); else
sex1 = cat("'",sex,"'",",");
run;

[Reeza]

Use the QUOTE() function.

proc sql noprint;
select distinct quote(sex) into :var_list separated by " " from sashelp.class;
quit;

%put &var_list.;

---

@soham_sas wrote:

Hi

i have a data set which contains some value (lets say a,b,c e.t.c) , now i have to create a macro for those values and that macro i have to use in a sql like (where name in (&macro)

so i am trying to concatenate the observations by single cotes and a comma but its not working

for example i am trying the sashelp.class

HAVE	WANT
F	'F',
M	'M'

also please note that in the last observation we don't need the comma to be there in the result

proc sql;
create table test1 as
select distinct sex from sashelp.classfit
order by sex;
quit;

data test2;
set test1;
by sex;
if last.sex = 1 then
sex1=cat("'",sex,"'"); else
sex1 = cat("'",sex,"'",",");
run;

---

[soham_sas]

@Reeza thanks for the solutions , but can you please suggest something like i have mentioned in the want column i.e single qoute with comma separated (like 'f','m')

[Reeza]

---

@soham_sas wrote:

@Reeza thanks for the solutions , but can you please suggest something like i have mentioned in the want column i.e single qoute with comma separated (like 'f','m')

---

How is that different than what I suggested?

EDIT: Are you really saying you can't add a  comma to the SEPARATED BY portion and look up the QUOTE function to figure out how to use single instead of double quotes and you want me to do it for you?

EDIT2: @soham_sas that wasn't an appropriate response. You can modify it by changing the SEPARATED BY portion - right now it's just adding spaces, and look into the QUOTE function to see how to have it generate single quotes instead of double quotes. If you're still having difficulty with this post what you've tried and explain what isn't working.

[ballardw]

Any takers on a bet that shortly we'll be seeing a "why am I getting a more positional parameters than defined " question?

[soham_sas]

@Reeza i didi it like below 

proc sql;
create table one as select distinct sex from sashelp.classfit
order by sex;
quit;

data two;
set one end=eof;
if eof then
sex_1 = cat("'",sex,"'");
else sex_1= cat("'",sex,"'",',');
run;

proc sql noprint;
select sex_1 into: var_list separated by " " from two;
quit;

%put &var_list;

[andreas_lds · Accepted solution]

---

@soham_sas wrote:

@Reeza i didi it like below 

proc sql;
create table one as select distinct sex from sashelp.classfit
order by sex;
quit;

data two;
set one end=eof;
if eof then
sex_1 = cat("'",sex,"'");
else sex_1= cat("'",sex,"'",',');
run;

proc sql noprint;
select sex_1 into: var_list separated by " " from two;
quit;

%put &var_list;

---

If you "need" the list in a macro variable, you don't need the data step.

proc sql noprint;
   select distinct quote(trim(Sex), "'") 
      into :varList separated by ','
      from sashelp.class
   ;
quit;