topic Concatenating same field by id in SAS Programming

Concatenating same field by id

deleted_user — Sun, 16 Aug 2009 02:42:23 GMT

I need the last 5 teachers' names concatenated for each course_code. At the end, I'd only have one record for each course_code that would contain course_code and teachers_names.

I thought I'd use PROC RANK to get the last 5 records for each course_code (using begin_date field). Now, how do I concatenate the names? Is there any other way of doing the whole thing more efficiently?

Dateset: sasuser.schedule
Course_Code Location Begin_Date Teacher
C001 Seattle 23-Oct-00 Hallis, Dr. George
C002 Dallas 4-Dec-00 Wickam, Dr. Alice
C003 Boston 8-Jan-01 Forest, Mr. Peter
C004 Seattle 22-Jan-01 Tally, Ms. Julia
C005 Dallas 26-Feb-01 Hallis, Dr. George
C006 Boston 2-Apr-01 Berthan, Ms. Judy
C001 Dallas 21-May-01 Hallis, Dr. George

Re: Concatenating same field by id

sbb — Sun, 16 Aug 2009 12:34:47 GMT

Using a DATA step, declare a "large enough" SAS variable to hold all names, also code a RETAIN statement to retain values across DATA step iterations, and use an assignment statement to concatenate values, with TRIM or CATT function.

Also, to be able to count the last five observations for a given "by group", you will need to do something like sort your file using a particular BY statement group, then assign an OBS_NUMBER variable. And then sort by DESCENDING OBS_NUM, and use this file to get your "last 'nn' observations".

Scott Barry
SBBWorks, Inc.

Re: Concatenating same field by id

data_null__ — Mon, 17 Aug 2009 13:17:21 GMT

You don't mention the criteria for determining the LAST 5. So I choose DATE. The IDGROUP option in PROC SUMMARY makes this very easy, assuming that this is what you want. Then you just use CATX to concatenate the 5 new variables.

[pre]
data sched;
input
Course_Code:$4.
Location :$16.
Begin_Date:date.
Teacher &$32.;
format BEG: date9.;
cards;
C001 Seattle 23-Oct-00 Hallis, Dr. George
C002 Dallas 4-Dec-00 Wickam, Dr. Alice
C003 Boston 8-Jan-01 Forest, Mr. Peter
C004 Seattle 22-Jan-01 Tally, Ms. Julia
C005 Dallas 26-Feb-01 Hallis, Dr. George
C006 Boston 2-Apr-01 Berthan, Ms. Judy
C001 Dallas 21-May-01 Hallis, Dr. George
;;;;
run;
proc summary nway;
class course:;
output out=wide(drop=_:)
idgroup(max(Beg:) out[5](teacher)=);
Run;
data wide;
set wide;
length teachers $256;
teachers = catx('; ', of teacher_:);
drop teacher_:;
run;
proc print;
run;
[/pre]

Re: Concatenating same field by id

deleted_user — Tue, 18 Aug 2009 03:08:14 GMT

Thanks sbb and data _null_ for your help. Data _null_, it was an unique way by the way.

The following is based on sbb's idea:

*method 2;
proc sort data=sasuser.schedule out=schedule; by course_code; run;
Data schedule;
set schedule(in=a) schedule(in=b);
by course_code;
if a then do;
Length teachers $ 200 ;
if first.course_code then teachers='';
teachers=catx('',teachers,teacher);
retain teachers; put _all_ '=';
end;
if b;
keep course_code teachers;
Run;

proc sort data=schedule nodupkey; by course_code; run;

One can perform the task using PROC TRANSPOSE also. The following is the code:
*method3;
proc sort data=sasuser.schedule out=schedule; by course_code; run;
proc transpose data=schedule
out=schedule;
var teacher;
by course_code;
run;

data schedule;
set schedule; length teachers $256;
teachers = catx('', COL1, COL2, COL3, COL4, COL5);
keep course_code teachers;
run;