BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
data_null__
Jade | Level 19
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]
deleted_user
Not applicable
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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 675 views
  • 0 likes
  • 3 in conversation