topic Re: concatenating variable names in SAS Programming

concatenating variable names

soham_sas — Tue, 06 Feb 2018 17:41:39 GMT

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;

Re: concatenating variable names

Reeza — Tue, 06 Feb 2018 17:44:48 GMT

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;

Re: concatenating variable names

soham_sas — Tue, 06 Feb 2018 18:22:28 GMT

@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')

Re: concatenating variable names

Reeza — Tue, 06 Feb 2018 22:23:21 GMT

@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.

Re: concatenating variable names

ballardw — Tue, 06 Feb 2018 19:16:06 GMT

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

Re: concatenating variable names

soham_sas — Wed, 07 Feb 2018 11:56:16 GMT

@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;

Re: concatenating variable names

andreas_lds — Wed, 07 Feb 2018 14:04:32 GMT

@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;