topic Re: pick observation from subgroups in SAS Procedures

pick observation from subgroups

deleted_user — Fri, 18 Feb 2011 21:10:35 GMT

Hi everyone,

I have data set from which I need to add the 1st, 2nd, 3rd, 4th, ..., nth value up in each subgroup.

For example, the data set looks like,

Name Year_count Starting_Year
A 7 1995
A 4 2000
B 2 1997
B 4 2000
C 3 2003
C 1 2006
C 2 2007
..................................................
X 5 1992
X 3 1997
X 1 2000

I want to put the first observation in A, B, C, ...., and X in a new group, the 2nd observation in A, B, C,... and X in a new group, and so on. The number of observation in each subgroup by name may vary, 2 for A, 2 for B, 3 for C,..., 3 for X.

the outcome will look like,
Name Year_count Starting_Year
A 7 1995
B 2 1997
C 3 2003
...............................................
X 5 1992

Please help me, thanks!

Re: pick observation from subgroups

ariari — Fri, 18 Feb 2011 22:00:43 GMT

Hi jie.su2134
pls. see following code. groupcount variable count number of group. A=1, B=2 ...
count variable calculate number in the each group.

proc sort data=x out x ; by name ; run ;

data x;
set x ;
by name ;

groupcount +1 ;
if first.name then count=1 ;
count+1;

if groupcount = count then output ;
run ;

Irena

Re: pick observation from subgroups

data_null__ — Fri, 18 Feb 2011 23:10:59 GMT

I don't know if I understand the question so this may be totally goofy.

[pre]
data ungrouped;
input Name:$1. Year_count Starting_Year;
cards;
A 7 1995
A 4 2000
B 2 1997
B 4 2000
C 3 2003
C 1 2006
C 2 2007
X 5 1992
X 3 1997
X 1 2000
;;;;
run;
data groupedV / view=groupedV;
do group=1 by 1 until(last.name);
set ungrouped;
by name;
output;
end;
run;
proc sort data=groupedV out=grouped;
by group;
run;
proc print;
run;
[/pre]

[pre]

Year_ Starting_
Obs group Name count Year

1 1 A 7 1995
2 1 B 2 1997
3 1 C 3 2003
4 1 X 5 1992
5 2 A 4 2000
6 2 B 4 2000
7 2 C 1 2006
8 2 X 3 1997
9 3 C 2 2007
10 3 X 1 2000
[/pre]

Re: pick observation from subgroups

deleted_user — Sat, 19 Feb 2011 19:25:00 GMT

Hi data_null,

I run the codes and they work exactly in the way I want!

Thanks!

Re: pick observation from subgroups

Ksharp — Mon, 21 Feb 2011 02:26:27 GMT

Hi.
if you like Hash Table which has high efficient.

[pre]
data ungrouped;
input Name:$1. Year_count Starting_Year;
cards;
A 7 1995
A 4 2000
B 2 1997
B 4 2000
C 3 2003
C 1 2006
C 2 2007
X 5 1992
X 3 1997
X 1 2000
;;;;
run;
data _null_;
declare hash group(ordered: 'a');
group.definekey('count','name');
group.definedata('name','year_count','starting_year');
group.definedone();

do until(last);
set ungrouped end=last;
if name ne lag(name) then count=0;
count+1;
group.add();
end;
group.output(dataset: 'grouped');
stop;
run;
[/pre]

Ksharp