topic Re: Create a concatenated string based on multiple observations in SAS Programming

Create a concatenated string based on multiple observations

tallkell — Mon, 25 Sep 2017 14:21:24 GMT

Hi all,

I would like to create a string variable that concactenates a variable from a multi-observation database into a longer string (left to right) then outputs this to a seperate single observation database. Example:

ID StringID

0001 ZB012

0001 XC045

0001 CV456

0001

0002 AS345

0002

0002 DE458

0003 RT784

0004 SD789

0004 PI066

0005

0006 TT545

0006 DS410

etc...

Output database:

ID longStringID

0001 ZB012, XC045, CV456

0002 AS345, DE458

0003 RT784

0004 SD789, PI066

0005

0006 TT545, DS410

Note: The ID with missing String ID should have an output observation. Thanks community.

I'm using SAS 9.4

--Tim

Re: Create a concatenated string based on multiple observations

ballardw — Mon, 25 Sep 2017 14:43:10 GMT

Do you know the maximum length possible for all combined strings for a given ID? The example below assumes a maximum of 100 characters. If you do not assign a length to longstringid the catx function will default to 200. If you need more than 200 definitely set a length.

data have;
infile datalines missover;
input ID  $          StringID $;
datalines;
0001        ZB012
0001        XC045
0001        CV456
0001        
0002        AS345
0002         
0002        DE458
0003        RT784
0004        SD789
0004        PI066
0005           
0006        TT545
0006        DS410
;
run;

data want;
   set have;
   by id;
   length longstringid $ 100.;
   retain longstringid ;
   if first.id then call missing(longstringid);
   if not missing(stringId) then longstringid =catx(', ',longstringid,stringid);
   if last.id;
run;

Re: Create a concatenated string based on multiple observations

Ksharp — Tue, 26 Sep 2017 13:35:23 GMT

data have;
infile datalines missover;
input ID  $          StringID $;
datalines;
0001        ZB012
0001        XC045
0001        CV456
0001        
0002        AS345
0002         
0002        DE458
0003        RT784
0004        SD789
0004        PI066
0005           
0006        TT545
0006        DS410
;
run;
data want;
 length want $ 200;
 do until(last.id);
  set have;
  by id;
  want=catx(',',want,StringID);
 end;
run;

Re: Create a concatenated string based on multiple observations

s_lassen — Thu, 28 Sep 2017 10:54:30 GMT

Ksharp,

In cases like this (repeatedly adding to the same string) you will get better performance by using CALL CATX than from using the CATX function:

data want;
 length want $ 200;
 do until(last.id);
  set have;
  by id;
  call catx(',',want,StringID);
 end;
run;