topic Concatenate multiple rows into a single string. in SAS Programming

Concatenate multiple rows into a single string.

YBM — Wed, 11 Jan 2017 21:48:23 GMT

Hi, I am frustrated after many tries and failing to concatinate multiple text variables into a long text string.

Lets assume that the text segments are PRIMEKEY in the data set PERSON_FILE (var1 var2 primekey var4 var5).

lets assume PRIMEKEY values are '123abc' '456fdc' '985liu'......

The desired result is a single string: '123abc','456fdc','985liu','...... :

I've tried 2 approaces which failed me:

approach 1:

DATA   RECKEYS (KEEP=SAMPLKEY);
SET    PERSON_FILE END=EOD;
   IF PRIMEKEY NOT EQ ' ' THEN DO;
       IF _N_ EQ 1 THEN SAMPLKEY="'"||PRIMEKEY||"'";
      ELSE SAMPLKEY=SAMPLKEY||",'"||PRIMEKEY||"'";
   END;
   IF _N_ GT &SAMPLE_SIZE OR EOD THEN DO;
         OUTPUT; STOP;
   END;
RUN;

The second approach that I cant make work is:

PROC SORT DATA=PERSON_FILE NODUPKEY; BY PRIMEKEY; RUN;

PROC SQL;
   SELECT DISTINCT "'"||SAMPLKEY||"'"
     INTO :RECKEY SEPARATED BY ','
     FROM KEYS;
QUIT; RUN;

TITLE 'RECKEY STRING';
PROC PRINT DATA=RECKEYS; RUN &DEBUG;

Re: Concatenate multiple rows into a single string.

HB — Wed, 11 Jan 2017 21:57:26 GMT

I am unclear as to what your data look like and what you are attempting.

Do you have:

var1  var2 primekey var4 var5
1       2      123abc    4      5
2       3      456fdc     5      6
3       4      985liu      6      7

or something like

var1  var2 primekey1 primekey2 primekey3 var4 var5
1       2      123abc     234asd      345qwe     4      5
2       3      456fdc     345dfg        678yui       5      6
3       4      985liu      678bnm      345dfg       6      7

or just what?

What do you have now and what should it look like?

Re: Concatenate multiple rows into a single string.

Astounding — Wed, 11 Jan 2017 22:37:08 GMT

Both approaches are possible! (Both might benefit from using the TRIM function in case your incoming string has trailing blanks that you want to get rid of.)

The first approach fails because the program assigns SAMPLKEY a length based on the first mention. So its length is the length of PRIMEKEY + 2 (enough to hold two single quotes, but not enough to hold the entire string you would want to assemble). So you could possible guess at a long length and issue this statement early in the DATA step:

length SAMPLKEY $32000;

The correct the code by adding TRIM:

if _n_=1 then samplkey = "'" || trim(primekey) || "'";

else samplkey = trim(samplkey) || ",'" || trim(primekey) || "'";

The second approach works as is (assuming that KEYS is the proper name for the incoming data set, not PERSON_FILE). The problem is that you don't understand what it creates. There is no output data set here that PROC PRINT can utilize. Instead, there is a macro variable created. You can see its contents by issuing this statement:

%put &reckey;

It too might benefit by adding the TRIM function.

Re: Concatenate multiple rows into a single string.

Reeza — Wed, 11 Jan 2017 23:02:13 GMT

What are you trying to do with this list?

The second option has a few other possibilities, but note there's a limit to the length of a string, 64K characters or 32K depending on your system.

Use the quote function to add quotation marks is a bit cleaner and then it creates a macro variable that you can use in further programs if required.

proc sql noprint;
select quote(trim(name)) into :name_list  separated by ", "
from sashelp.class;
quit;

%PUT &name_list;

Re: Concatenate multiple rows into a single string.

PGStats — Wed, 11 Jan 2017 23:18:50 GMT

Try something like:

DATA RECKEYS(KEEP=SAMPLKEY);
length samplkey $2000;
do until(eod or i=&sample_size); 
    SET PERSON_FILE END=EOD;
    if lengthn(primekey) > 0 then do;
       samplkey = catx(",", samplkey, catq("2AS", primekey));
       i + 1;
       end;
    end;
output; stop;
run;

Re: Concatenate multiple rows into a single string.

YBM — Thu, 12 Jan 2017 14:12:21 GMT

Var1 var2 samplkey var4 ...

25 Jeff 123abc m

32 Hu 456fdc m

45 Waii 985liou f

The result strings the sample keys, each in quotes and separated by a comma.

Re: Concatenate multiple rows into a single string.

HB — Thu, 12 Jan 2017 16:01:48 GMT

Ah. I see. How many records are we talking and how long will this key be? And what on earth will you do with a giant text string?

Re: Concatenate multiple rows into a single string.

YBM — Thu, 12 Jan 2017 21:12:36 GMT

I have not tried this solution. YBM.

Re: Concatenate multiple rows into a single string.

YBM — Thu, 12 Jan 2017 21:14:12 GMT

the first situation.

Re: Concatenate multiple rows into a single string.

YBM — Thu, 12 Jan 2017 21:15:55 GMT

Yours is the one I tried. Since it works, I have not tried the others' suggestions. Thanks.