DATA Step, Macro, Functions and more

concatenating variable names

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

concatenating variable names

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

 

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


Accepted Solutions
Solution
‎02-09-2018 07:00 AM
Valued Guide
Posts: 629

Re: concatenating variable names

Posted in reply to soham_sas

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;

View solution in original post


All Replies
Super User
Posts: 24,012

Re: concatenating variable names

Posted in reply to soham_sas

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;


 

Contributor
Posts: 49

Re: concatenating variable names

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

Super User
Posts: 24,012

Re: concatenating variable names

[ Edited ]
Posted in reply to soham_sas

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.

Super User
Posts: 13,941

Re: concatenating variable names

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

Contributor
Posts: 49

Re: concatenating variable names

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

Solution
‎02-09-2018 07:00 AM
Valued Guide
Posts: 629

Re: concatenating variable names

Posted in reply to soham_sas

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 156 views
  • 3 likes
  • 4 in conversation