SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

Hello

In this example I created a macro variable that get value  Peter,Paul

My question is how to modify the code to get values "peter","Paul"

The reason is that I want to use where clause

data name_list;
length name $10;
Input name $;
datalines;
Peter
John
Paul
David
;
run;


proc sql noprint;
select name
into :names separated by ',' 
from name_list
where substr(name,1,1) = 'P';
quit;
%put &names;
/*Peter,Paul*/



data output;
set name_list;
where name in ("&names");
run;

 

 

 


 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

What would you use in a data-step to add quotes to a string? Using the functions quote and trim in the select-clause solves the problem:

 

proc sql noprint;
   select quote(trim(Name))
      into :names separated by ','
      from name_List
         where Name like 'P%'
   ;
quit;

Now you need to modify the last step:

data output;
  set name_list;
  where name in (&names.);
run;

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

 

into :names separated by '","'...where name in ("&names."); 

 

However you really do not need to do this.  Simply use a sub-query, it is far easier to program with, and expandable beyond the realms of macro language:

proc sql;
  create table name_list as
  select *
  from   name_list
  where  name in (select name from name_list where substr(name,1,1)="P");
quit;

 In fact, (and without seeinig the rest of the process), you will most likely be better off creating flags in your data, and just putting where's in where necessary, but can't really tell as only seeing one small part.

Ronein
Meteorite | Level 14

Thanks

It is not working.I get error 49

 

 

proc sql noprint;
select name
into :names separated by '","'
from name_list
where substr(name,1,1) = 'P';
quit;
%put &names;
Kurt_Bremser
Super User
proc sql noprint;
select quote(name)
into :names separated by ','
from name_list
where substr(name,1,1) = 'P';
quit;

But, to repeat for the x-th time, it's not necessary at all.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I do not get any error when running:

data name_list;
length name $10;
Input name $;
datalines;
Peter
John
Paul
David
;
run;
proc sql noprint;
select name
into :names separated by '","'
from name_list
where substr(name,1,1) = 'P';
quit;
%put &names;

Post your log in future, as text, I do note know what error 49 is or how you got it.

Kurt_Bremser
Super User

No need for the macro variable, it's best done in SQL with a sub-select:

proc sql;
create table output as select *
from name_list
where name in (select name from name_list where substr(name,1,1) = 'P');
quit;

Of course, since it's all from one table, it can be made still simpler:

data output;
set name_list;
where substr(name,1,1) = 'P';
run;
andreas_lds
Jade | Level 19

What would you use in a data-step to add quotes to a string? Using the functions quote and trim in the select-clause solves the problem:

 

proc sql noprint;
   select quote(trim(Name))
      into :names separated by ','
      from name_List
         where Name like 'P%'
   ;
quit;

Now you need to modify the last step:

data output;
  set name_list;
  where name in (&names.);
run;

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 30952 views
  • 6 likes
  • 4 in conversation