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
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.