DATA Step, Macro, Functions and more

Concatenate vertically in macrovariable

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Concatenate vertically in macrovariable

[ Edited ]

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


Accepted Solutions
Solution
‎06-30-2017 07:45 AM
Super User
Super User
Posts: 6,502

Re: Concatenate vertically in macrovariable

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


All Replies
Super User
Posts: 17,907

Re: Concatenate vertically in macrovariable

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;
Solution
‎06-30-2017 07:45 AM
Super User
Super User
Posts: 6,502

Re: Concatenate vertically in macrovariable

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;
PROC Star
Posts: 7,364

Re: Concatenate vertically in macrovariable

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

 

Art, CEO, AnalystFinder.com

 

Super User
Super User
Posts: 6,502

Re: Concatenate vertically in macrovariable


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):
Contributor
Posts: 45

Re: Concatenate vertically in macrovariable

Thanks Tom,

 

It worked well. Smiley Happy

Contributor
Posts: 45

Re: Concatenate vertically in macrovariable

[ Edited ]

I answered too quickly, it is not working Smiley Sad

 

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

Super User
Super User
Posts: 6,502

Re: Concatenate vertically in macrovariable

[ Edited ]

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;

 

PROC Star
Posts: 7,364

Re: Concatenate vertically in macrovariable

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

 

Super User
Posts: 17,907

Re: Concatenate vertically in macrovariable

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

Re: Concatenate vertically in macrovariable

[ Edited ]

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.

 

Super User
Super User
Posts: 6,502

Re: Concatenate vertically in macrovariable


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.

Contributor
Posts: 45

Re: Concatenate vertically in macrovariable

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

 

Super User
Super User
Posts: 6,502

Re: Concatenate vertically in macrovariable


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) )
Contributor
Posts: 45

Re: Concatenate vertically in macrovariable

It was exactly the solution.

Thanks so much Tom! Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 194 views
  • 1 like
  • 4 in conversation