topic Re: Concatenating column values into macro variable in SAS Programming

Concatenating column values into macro variable

A_SAS_Man — Wed, 29 Sep 2021 15:11:17 GMT

I am attempting to concatenate all values in a column into one macro variable with some additional characters. Here is what I'm starting with.

data have;
input test_values :$2.;
datalines;
AB
BC
CD
DE
EF
;

What I would like is for the values to be stored in a macro with the following structure. Assuming we were storing into a macro variable named test and I ran

%put &test;

I would like to see the following:

'AB','BC','CD','DE','EF'

I have been trying to use some variation of the following to do it, but haven't been successful yet.

proc sql noprint;
select test_values into: test separated by ','
from have;
quit;

Re: Concatenating column values into macro variable

PaigeMiller — Wed, 29 Sep 2021 15:16:38 GMT

Let the QUOTE() function have all the fun.

proc sql noprint;
select quote(test_values,"'") into: test separated by ','
from have;
quit;

Re: Concatenating column values into macro variable

ballardw — Wed, 29 Sep 2021 17:18:23 GMT

Depending on the defined length of the text variable you may need: quote(strip(test_values),"'") to avoid trailing blanks inside the quotes for any value not the full defined length of the variable.

Re: Concatenating column values into macro variable

Tom — Wed, 29 Sep 2021 17:41:31 GMT

You normally would not want to use STRIP(). That will remove both leading and trailing spaces. SAS comparisons normally ignore trailing spaces, but leadings spaces it considers meaningful. So use TRIM() instead.

Re: Concatenating column values into macro variable

Peter_C — Wed, 29 Sep 2021 20:57:14 GMT

alternatively
select test_values format= $quote200. into: test separated by ','

deals with trailing spaces as required and highlights that we can apply useful formats when SELECT-ing INTO :macrovars