DATA Step, Macro, Functions and more

Concatenating same field by id

Reply
N/A
Posts: 0

Concatenating same field by id

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Concatenating same field by id

Posted in reply to deleted_user
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.
Respected Advisor
Posts: 3,799

Re: Concatenating same field by id

Posted in reply to deleted_user
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=_Smiley Happy
idgroup(max(BegSmiley Happy out[5](teacher)=);
Run;
data wide;
set wide;
length teachers $256;
teachers = catx('; ', of teacher_Smiley Happy;
drop teacher_:;
run;
proc print;
run;
[/pre]
N/A
Posts: 0

Re: Concatenating same field by id

Posted in reply to deleted_user
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;
Ask a Question
Discussion stats
  • 3 replies
  • 131 views
  • 0 likes
  • 3 in conversation