topic Re: Concatenate vertically in macrovariable in SAS Programming

Concatenate vertically in macrovariable

Planck — Thu, 29 Jun 2017 18:14:16 GMT

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

Re: Concatenate vertically in macrovariable

Reeza — Thu, 29 Jun 2017 18:18:51 GMT

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;

Re: Concatenate vertically in macrovariable

Tom — Thu, 29 Jun 2017 18:20:08 GMT

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;

Re: Concatenate vertically in macrovariable

art297 — Thu, 29 Jun 2017 19:16:43 GMT

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

Art, CEO, AnalystFinder.com

Re: Concatenate vertically in macrovariable

Tom — Thu, 29 Jun 2017 19:45:30 GMT

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

Re: Concatenate vertically in macrovariable

Planck — Fri, 30 Jun 2017 11:46:54 GMT

Thanks Tom,

It worked well. 🙂

Re: Concatenate vertically in macrovariable

Planck — Fri, 30 Jun 2017 12:57:54 GMT

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

Re: Concatenate vertically in macrovariable

Tom — Fri, 30 Jun 2017 13:20:56 GMT

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;

Re: Concatenate vertically in macrovariable

art297 — Fri, 30 Jun 2017 13:18:04 GMT

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

Re: Concatenate vertically in macrovariable

Planck — Fri, 30 Jun 2017 13:20:32 GMT

Oh ok thanks for answer,

I am used to put some commas. But even, when I try your method i get an error.

The first query works well. I get the result: 1 5 13

But after when I run the second query so I have:

WHERE mydata IN (1 5 13)

And I get the error:

ERROR: Expression using IN has components that are of different data types.

because mydata is character.

Re: Concatenate vertically in macrovariable

Reeza — Fri, 30 Jun 2017 13:20:37 GMT

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

Re: Concatenate vertically in macrovariable

Reeza — Fri, 30 Jun 2017 13:21:54 GMT

If your data is character you need the quotes.

Re: Concatenate vertically in macrovariable

Tom — Fri, 30 Jun 2017 13:22:59 GMT

@Planck wrote:

Oh ok thanks for answer,

I am used to put some commas. But even, when I try your method i get an error.

The first query works well. I get the result: 1 5 13

But after when I run the second query so I have:
WHERE mydata IN (1 5 13)
And I get the error:
ERROR: Expression using IN has components that are of different data types.
because mydata is character.

The error message explains the problem. You cannot use numeric literals in the IN list if the variable being compared is character. You need to use character literals.

WHERE mydata IN ("1" "5" "13")

Re: Concatenate vertically in macrovariable

Planck — Fri, 30 Jun 2017 13:50:05 GMT

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.

Re: Concatenate vertically in macrovariable

Tom — Fri, 30 Jun 2017 14:03:47 GMT

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

Re: Concatenate vertically in macrovariable

Planck — Fri, 30 Jun 2017 14:11:05 GMT

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

WHERE mydata IN (&namelist.)

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

Re: Concatenate vertically in macrovariable

Tom — Fri, 30 Jun 2017 14:15:59 GMT

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

Re: Concatenate vertically in macrovariable

Planck — Fri, 30 Jun 2017 14:44:18 GMT

It was exactly the solution.

Thanks so much Tom! 🙂