BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
4 REPLIES 4
ariari
Calcite | Level 5
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
data_null__
Jade | Level 19
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]
deleted_user
Not applicable
Hi data_null,

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

Thanks!
Ksharp
Super User
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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 1302 views
  • 0 likes
  • 4 in conversation