BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
soham_sas
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

@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

6 REPLIES 6
Reeza
Super User

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
Quartz | Level 8

@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
Super User

@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
Super User

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
Quartz | Level 8

@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
Jade | Level 19

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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