BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
I was using the first.byvar and last.byvar approach to no avail.

I'll give the transpose solution a whirl.

Thanks!
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Peter_C
Rhodochrosite | Level 12
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
DanielSantos
Barite | Level 11
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 761 views
  • 0 likes
  • 5 in conversation