topic Re: Concatenating cross observations in SAS Programming

Concatenating cross observations

mnew — Tue, 29 Mar 2011 17:43:59 GMT

Experts:

Another beginner question. How do you use the values in different observations as arguments for a function? I'm trying to concatenate string values from multiple observations. I'll use the sasuser.pilots as an example. Each observation has a person's name etc. What if you need to generate name lists so you can assign these people to teams of five?

Here's very clumsy code I managed to come up with. I'm sure it is not the way to go because if the dataset has hundreds of people, I would be in real misery :). Maybe my trouble is also not knowing how to use a do loop here.

Thank you for your time in advance.

data work.NameList;
set sasuser.pilots(keep=lastname)End=last ;
Length GroupList $ 500;
retain GroupList;
if _n_ < 11 then do;
Counter+1;
Grouplist=Catx(';',Grouplist,LastName);
if _n_=10 then do;
output;
GroupList='';
Counter=0;
end;
end;
if 11 <=_n_<=15 then do;
Counter+1;
Grouplist=Catx(';',Grouplist,LastName);
if _n_=15 then do;
output;
GroupList='';
Counter=0;
end;
end;
if _n_>15 then do;
Counter+1;
Grouplist=Catx(';',Grouplist,LastName);
if last=1 then output;
end;
run;
proc print data=work.namelist;
run;

Re: Concatenating cross observations

SPR — Tue, 29 Mar 2011 18:17:40 GMT

Hello Mnew,

This is a possible solution. It could be much easier if you need only team number.
[pre]
proc SQL;
create table n as
select distinct name as name from sashelp.class
;quit;
data g;
length team $100;
retain team;
set n;
if _n_=1 then team="";
if MOD(_n_-1,5) = 0 then do; g+1; team=""; end;
team=TRIM(team)||TRIM(name)||";";
run;
proc SQL;
create table g1 as
select a.name,a.g,b.team
from g as a left join (select g, max(team) as team from g group by g) as b
on a.g=b.g
;quit;
proc SQL;
create table r as
select a.*, b.g, b.team
from sashelp.class as a left join g1 as b on a.name=b.name
;quit;
[/pre]
Sincerely,
SPR

Re: Concatenating cross observations

mnew — Tue, 29 Mar 2011 23:04:17 GMT

Thank you so much!
I went back and revised my code. It's less clumsy now. You introduced the MOD function to me :).
I've signed up for the SAS SQL training so I will review your code after that study...

data work.NameList;
set sasuser.pilots(keep=lastname)End=last ;
Length GroupList $ 500;
retain GroupList;
Grouplist=Catx(';',Grouplist,LastName);
if mod(_n_,5)=0 then do;
output;
GroupList='';
end;
else if last=1 then output;
drop lastname;
run;
proc print data=work.namelist;
run;