topic Re: Trying to concatenate strings from multiple obs into 1 obs. in SAS Procedures

Trying to concatenate strings from multiple obs into 1 obs.

jerry898969 — Mon, 28 Sep 2009 18:03:14 GMT

Hello to all,
I've run into an issue that I need a bit of help with.

I have a table that has a ID, ONum, notes columns.
What i'm trying to do concatenate every notes obs into one obs based on similer
ID & ONum with a space inbetween every concatenation.

ID ONum notes
-- --------- ----------
11 10001 "This is a Test"
11 10001 " of SAS"
11 10002 "Today the weather was good"
11 10002 "Busy Shopping"
44 19999 "Fishing"
55 77777 "Working on car"
55 77777 "Hunting"
55 77777 "Swimming"

After data step I need the data to look like this

ID ONum notes
-- --------- ----------
11 10001 "This is a Test of SAS"
11 10002 "Today the weather was good Busy Shopping"
44 19999 "Fishing"
55 77777 "Working on car Hunting Swimming"

Thank you to anyone who can help

Re: Trying to concatenate strings from multiple obs into 1 obs.

deleted_user — Mon, 28 Sep 2009 18:30:25 GMT

One approach:

proc sort data=test out=sorted;
by id Onum;
run;

data concat;
length total_notes $ 200 /* need to be the max */;
set sorted;
by ID ONum;

retain total_notes;

if first.Onum then total_notes = notes;
else
total_notes=trim(total_notes) || ' ' || trim(notes);

if last.ONum then output;
run;

Re: Trying to concatenate strings from multiple obs into 1 obs.

jerry898969 — Mon, 28 Sep 2009 18:39:48 GMT

kmg,
Thank you so much I think that did it.

Do you know if there is a way to put a return between the notes?

Currently the field is defined as length notes $32767. If the field is defined as that big is there anything bigger? I have no control over the field size i'm just afraid that if they have 4 rows that have almost that many characters what will happen when it tries to concatenate them?

Thanks again for all your help

Re: Trying to concatenate strings from multiple obs into 1 obs.

barheat — Mon, 28 Sep 2009 18:44:48 GMT

Jerry:

Assume that your data is in a table called temp. First step is to transpose the data.

proc transpose data=temp out=trans;
var notes;
by id onum;
run;

Second step is to use the catx function to concatenate the columns.

data trans;
set trans;
new_notes=catx(' ',col1,col2,col3);
keep id onum new_notes;
run;

The issue for a program is to determine the maximum frequency for the id onum combination. This could be done in a macro to specify the number of col arguments for CATX

Re: Trying to concatenate strings from multiple obs into 1 obs.

jerry898969 — Mon, 28 Sep 2009 18:46:41 GMT

Barheat,
Thank you so much for your help. I'm going to try what you suggested.

Thank You

Re: Trying to concatenate strings from multiple obs into 1 obs.

deleted_user — Mon, 28 Sep 2009 19:26:00 GMT

Note that the Proc Transpose approach as given assumes that you can specify what the maximum number of strings will be (in your example, it was 3, but it may vary from data set to data set). It would be better to switch to using an array to avoid this limitation.

Re: Trying to concatenate strings from multiple obs into 1 obs.

jerry898969 — Mon, 28 Sep 2009 19:53:41 GMT

kmg,
I was just going to post a question about that.
I'm going to stick with you example for now. It seems to be working good for now.

Thank you so much to both of you.

Re: Trying to concatenate strings from multiple obs into 1 obs.

sbb — Mon, 28 Sep 2009 21:52:07 GMT

While using the PROC TRANSPOSE approach, precede the PROC execution with a DATA step that counts and increments a variable every "n" times, then you have flexibility controlling the transposed observation range.

Scott Barry
SBBWorks, Inc.

Re: Trying to concatenate strings from multiple obs into 1 obs.

data_null__ — Tue, 29 Sep 2009 13:53:08 GMT

The idea to PROC TRANSPOSE and CATX is good because you don't need to know HOW MANY. Also CATX will print warning if the concatenation will not fit in the variable, see example below.

[pre]
data test;
input ID:$2. ONum:$5. notes &$quote64.;
cards;
11 10001 "This is a Test"
11 10001 " of SAS"
11 10002 "Today the weather was good"
11 10002 "Busy Shopping"
44 19999 "Fishing"
55 77777 "Working on car"
55 77777 "Hunting"
55 77777 "Swimming"
;;;;
run;
proc print;
run;
proc transpose out=wide(drop=_name_);
by id onum;
var notes;
run;
data wide;
length note $35; *To test overrun;
set wide;
note = catx(' ',of col:);
drop col:;
run;
proc print;
run;
[/pre]

Re: Trying to concatenate strings from multiple obs into 1 obs.

ballardw — Thu, 01 Oct 2009 19:42:39 GMT

> kmg,
>
> Do you know if there is a way to put a return between
> the notes?

From the first response modify:

if last.ONum then output;
run;

To:
if last.ONum then do;
output;
total_notes= "";
output;/* this puts a blank value for the note field, insert appropriate character code if you want an actual return character */
end;
run;

>
> Currently the field is defined as length notes
> $32767. If the field is defined as that big is there
> anything bigger? I have no control over the field
> size i'm just afraid that if they have 4 rows that
> have almost that many characters what will happen
> when it tries to concatenate them?
>

No clue but some create use of a FILE statement and PUT with @ and @@ may create a text file without that size limitation.

Re: Trying to concatenate strings from multiple obs into 1 obs.

ravi_mandal — Tue, 09 Aug 2016 19:30:46 GMT

Data task;
Input ID ONum notes & $ 30.;
cards;
11 10001 This is a Test
11 10001 of SAS
11 10002 Today the weather was good
11 10002 Busy Shopping
44 19999 Fishing
55 77777 Working on car
55 77777 Hunting
55 77777 Swimming
;
run;

Data task_final;
retain id onum ;
length new $ 50;
do until (last.onum);
set task;
by onum;
new=catx(" ",new,notes);
end;
Keep ID ONum new;
run;