topic Re: Concatenating Character Data in SAS Procedures

Concatenating Character Data

deleted_user — Wed, 15 Apr 2009 12:49:24 GMT

I
have data that looks like this:

L_NAME
COMMENT
Smith
Comment1
Smith
Comment2
Smith
Comment3
Smith
Comment4
Jones
Comment1
Jones
Comment2
Jones
Comment3

There can be up to 5 comments per last
name. I want to get all 4 comments for Smith (for example)
concatenated on one line such as this:

L_NAME
COMMENT
Smith
Comment1Comment2Comment3Comment4

I usually zip
through these data step problems but this one is giving me fits.

Any
hints are greatly appreciated!

Re: Concatenating Character Data

Cynthia_sas — Wed, 15 Apr 2009 13:14:26 GMT

Hi:
You could either do it with FIRST.byvar and LAST.byvar in a datastep. Every time you read a new obs, you could concatenate that obs value for comment onto a new comment variable whose value would be retained until you got to the last obs of the group.

Or, you could use PROC TRANSPOSE by L_NAME to get the comments all on one obs and then just concatenate all the 5 possible comments together.

One possible transpose solution is shown below.

cynthia
[pre]
** Read data and make an "origord" var;
** to keep comments in the order they appeared in;
** the original input data.;
data allcom;
length l_name $25 comment $30;
infile datalines;
input l_name & $ comment & $;
origord = _n_;
return;
datalines;
Smith One Fish
Smith Two Fish
Smith Red Fish
Smith Blue Fish
Jones Twas brillig and
Jones the slithy toves did
Jones gyre and gimble in the wabe
;
run;

** Sort the data;
proc sort data=allcom;
by l_name origord;
run;

** Transpose the data;
proc transpose data=allcom out=tr_out;
by l_name;
var comment;
run;

** Make the NewComment variable;
data newcom (keep=l_name newcomment);
length col1 col2 col3 col4 col5 $30 newcomment $150;
set tr_out;

newcomment = catx(' ',col1, col2, col3, col4, col5);
run;

** Print the new file;
proc print data=newcom;
run;
[/pre]

Re: Concatenating Character Data

deleted_user — Wed, 15 Apr 2009 13:31:10 GMT

I was using the first.byvar and last.byvar approach to no avail.

I'll give the transpose solution a whirl.

Thanks!

Re: Concatenating Character Data

sbb — Wed, 15 Apr 2009 17:21:51 GMT

Check your SAS log output for a diagnostic message, such as:

NOTE: VARIABLE FIRST. IS UNINITIALIZED

indicating that you have forgotten to code a BY statement in your DATA step.

Scott Barry
SBBWorks, Inc.

Re: Concatenating Character Data

Peter_C — Thu, 16 Apr 2009 09:29:14 GMT

Dorfman and Whitlock are names of experts who in discussion together came up with an approach that moves the SET statement away from the top of a data step into a loop. The approach is acquiring the title DoW loop.
Here it is most helpful[pre] data reduced( keep= L_name comment compress=yes ) ;
length comment $2000 ; * surely enough, but can be wider ;
do until( last.L_name ) ;
set full_data_set( rename=( comment= oneComment )) ;
by L_name ;
comment= trimn( comment ) !! oneComment ;
end ;
run ; [/pre]The merit of DoW looping is brief code using the nature of the data step to help solve the problem.
See Paul Dorfman's 2009 paper on the topic at http://support.sas.com/resources/papers/proceedings09/038-2009.pdf

I'm sure understanding and mastering the technique is an important stage in learning to program in a SAS System environment.

PeterC

Re: Concatenating Character Data

DanielSantos — Thu, 16 Apr 2009 10:30:43 GMT

Hello Peter.

Great reply.

The wonderful magic of the DoW loop technique and its many variations.

I attended Paul's presentation about the DoW loop at SGF2009, for me one of the best papers.
I also loved the presentation about the "hardcore" performance POKE/PEEK functions (see: http://support.sas.com/resources/papers/proceedings09/010-2009.pdf ).

Greetings from Portugal.

Daniel Santos at www.cgd.pt