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

Hi,

 

%macro extraction;

%LET A = '(' ;

data _null_;
set tableT ;
where B = "B1" and C IN ('C1','C2','C3');
&A. = &A. || ',';
run;

&A. = &A. || ')';


%mend;

%extraction;

I have this code with several error.

 

I try to concatenate all D column values of the result of my datastep in a macro variable named A in this way:

"(D_firstrow, D_secondrow, ..., D_nthrow)"

 

Thanks for help

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

I think you are asking for the SELECT ... INTO functionality of PROC SQL.

Try running this example:

proc sql noprint;
  select quote(trim(name)) into :namelist separate by ' '
  from sashelp.class
  where age =13
  ;
quit;
%put &=namelist;
proc print data=sashelp.class ;
 where name in (&namelist);
run;

View solution in original post

17 REPLIES 17
Reeza
Super User

Can you explain what you're trying to do? Is there any reason the SQL version isn't being used, it's much easier?

 

proc sql;
select name into :name_list separated by ", "
from sashelp.class;
quit;
Tom
Super User Tom
Super User

I think you are asking for the SELECT ... INTO functionality of PROC SQL.

Try running this example:

proc sql noprint;
  select quote(trim(name)) into :namelist separate by ' '
  from sashelp.class
  where age =13
  ;
quit;
%put &=namelist;
proc print data=sashelp.class ;
 where name in (&namelist);
run;
art297
Opal | Level 21

One minor correction to @Tom's code. Surely he meant separtated rather than separate.

 

Art, CEO, AnalystFinder.com

 

Tom
Super User Tom
Super User

@art297 wrote:

One minor correction to @Tom's code. Surely he meant separtated rather than separate.

 

Art, CEO, AnalystFinder.com

 


Thanks.  Note that SAS auto-corrected it so it actually ran.

97   proc sql noprint;
98     select quote(trim(name)) into :namelist separate by ' '
                                               --------
                                               1
WARNING 1-322: Assuming the symbol SEPARATED was misspelled as separate.

99     from sashelp.class
100    where age =13
101    ;
102  quit;
NOTE: PROCEDURE SQL used (Total process time):
Planck
Obsidian | Level 7

Thanks Tom,

 

It worked well. 🙂

Planck
Obsidian | Level 7

I answered too quickly, it is not working 😞

 

If I took back your example:

proc sql noprint;
  select quote(trim(name)) into :namelist separated by "','"
  from sashelp.class
  where age =13
  ;
quit;
%put &=namelist;
proc print data=sashelp.class ;
 where name in ('&namelist.');
run;

I modified the separation string because it is character.

 

I need to have " '01','02','03' " and not "1 2 3"

 

So I create the macrovariable namelist with value " 01','02','03  " and then I call ('&namelist.') to get " ('01','02','03') " but thers should be something wrong because it returns no result in the new table, whereas when I write ('01','02','03') manually i have some results.

 

 

PS: when I write 

%put &namelist.

I get 01','02','03

 

which is the expected result.

Can you help me for this?

Thanks

Tom
Super User Tom
Super User

This type of syntax works fine in SAS code.

where name in ("Alfred" "Beth" "Nancy")

There is no need to use single quotes instead of double quotes and no need to add commas between the values instead of spaces.

 

What are you using the list of values for?  If you are trying to generated a comma separated list of values quoted using single quote characters so that you use the value in some pass thru SQL to a database that is not as friendly to code in then your query will need to work a little harder.

 

You could first add the single quotes and commas using the SEPARATED BY clause

select name into :list separated by "','"

But then you need to add the beginning and ending single quotes, which can get tricky.  You should be able to use the %BQUOTE() macro function which will let you expand the macro variable inside of single quotes, but you would also want to add the %UNQUOTE() macro function to remove the macro quoting that %BQUOTE() will add.

%let list=%unquote(%bquote('&list'));

 

Note that method will have problems if your data step variable could contain actual single quote characters. For example O'Hara could be valid NAME value.  In that case perhaps it is better to have SAS functions add the quotes and not use macro coding to do that.

select quote(trim(name),"'") into :list separated by ','

Now that you have your single quoted comma separated list in a IN () operation.

where name in (&list)

Or perhaps in a DO loop in a data step where the commas are needed.

do name = &list ;
   output;
end;

 

art297
Opal | Level 21

If you need single quotes, separated by commas, you could use:

proc sql noprint;
  select catt("'",(trim(name)),"'") into :namelist separated by ','
  from sashelp.class
  where age =13
  ;
quit;
%put &namelist;
proc print data=sashelp.class ;
 where name in (&namelist);
run;

Art, CEO, AnalystFinder.com

 

Reeza
Super User
Proc sql noprint;
Select quote(name, "'") into :name_list separated by ', ' from SASHELP.class;
Quit;
Planck
Obsidian | Level 7

Thanks for the answer art297.

 

But I get another error then...

Doing this it returns '1','5','13' which is a good answer, but ...

 

NOTE: Line generated by the macro variable "namelist".
148        '1','5','13'
           _
           22
            _
            76

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, 
              a missing value, (, -, SELECT.  
ERROR 76-322: Syntax error, statement will be ignored.

 

Tom
Super User Tom
Super User

@Planck wrote:

Thanks for the answer art297.

 

But I get another error then...

Doing this it returns '1','5','13' which is a good answer, but ...

 

NOTE: Line generated by the macro variable "namelist".
148        '1','5','13'
           _
           22
            _
            76

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, 
              a missing value, (, -, SELECT.  
ERROR 76-322: Syntax error, statement will be ignored.

 


You have not provided enough context to evaluate the cause of the error.

Most likely you have ommited the () in trying to use the IN operator.

Planck
Obsidian | Level 7

I checked this but no. What is strange is when I replace manually 

WHERE mydata IN (&namelist.)

by

WHERE mydata IN ('1','5','13')

It works...

That's annonying a bit ^^

 

But when it is &namelist. it keeps telling me this error when trying to replace the value in PROC SQL

 

Tom
Super User Tom
Super User

@Planck wrote:

I checked this but no. What is strange is when I replace manually 

WHERE mydata IN (&namelist.)

by

WHERE mydata IN ('1','5','13')

It works...

That's annonying a bit ^^

 

But when it is &namelist. it keeps telling me this error when trying to replace the value in PROC SQL

 


Most likely your macro variable has macro quoting and it is confusing the SAS compiler.  Try removing the macro quoting.

where mydata in ( %unquote(&namelist) )
Planck
Obsidian | Level 7

It was exactly the solution.

Thanks so much Tom! 🙂

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
  • 17 replies
  • 2128 views
  • 1 like
  • 4 in conversation