Concatenate vertically in macrovariable

[Planck]

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

[Reeza]

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 · Accepted solution]

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]

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

Art, CEO, AnalystFinder.com

[Tom]

---

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

Thanks Tom,

It worked well. 🙂

[Planck]

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]

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]

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]

Proc sql noprint;
Select quote(name, "'") into :name_list separated by ', ' from SASHELP.class;
Quit;

[Planck]

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]

---

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

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]

---

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

It was exactly the solution.

Thanks so much Tom! 🙂