Obsidian | Level 7

## Count the number of Observation by variable and then divide them by 4 and create new coloumn

data test;

input spool_number \$ dose number batch \$;

datalines;

su 30 1 rx1

su 30 2 rx1

su 30 3 rx1

su 30 4 rx1

1 30 5 rx1

2 30 6 rx1

sp 30 7 rx1

su 30 8 rx1

4 30 9 rx1

5 30 10 rx1

6 30 11 rx1

su 14 12 rx1

su 14 13 rx1

su 14 14 rx1

su 14 15 rx1

1 14 16 rx1

2 14 17 rx1

3 14 18 rx1

su 14 19 rx1

4 14 20 rx1

5 14 21 rx1

failure 14 22 rx1

su 14 23 rx1

su 14 24 rx1

su 14 25 rx1

1 14 26 rx1

2 14 27 rx1

su 30 1 rx2

su 30 2 rx2

su 30 3 rx2

su 30 4 rx2

1 30 5 rx2

2 30 6 rx2

sp 30 7 rx2

su 30 8 rx2

4 30 9 rx2

5 30 10 rx2

6 30 11 rx2

su 14 12 rx2

su 14 13 rx2

su 14 14 rx2

su 14 15 rx2

1 14 16 rx2

2 14 17 rx2

3 14 18 rx2

su 14 19 rx2

4 14 20 rx2

5 14 21 rx2

failure 14 22 rx2

su 14 23 rx2

su 14 24 rx2

su 14 25 rx2

1 14 26 rx2

2 14 27 rx2

run;

data have2;

set have ;

by batch notsorted;

if first.batch then count=0;

count+1;

if last.batch then output;

run;

proc print data=have2;

run;

Now I want to divide the count by 4 for each batch and then create new coloumn with value of q1 q2 q3 and q4.

Hope it make sense,.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Onyx | Level 15

## Re: Count the number of Observation by variable and then divide them by 4 and create new coloumn

Not sure if you want the 'q' or not, here is the version with 'q', it gets simpler without 'q',

data want;
do _n_=1 by 1 until (last.batch);
set test;
by batch notsorted;
end;
_cyc=ceil(_n_/4);
do _n_=1 by 1 until (last.batch);
set test;
by batch notsorted;
new_var=cats('q', ceil(_n_/_cyc));
output;
end;
drop _cyc;
run;
5 REPLIES 5
Diamond | Level 26

## Re: Count the number of Observation by variable and then divide them by 4 and create new coloumn

data have2;
set have ;
by batch notsorted;
if first.batch then count=0;
count+1;
qtr=cats("Q",put(ceil(count/4),best.));
if last.batch then output;
run;
Tourmaline | Level 20

## Re: Count the number of Observation by variable and then divide them by 4 and create new coloumn

So it's not an aggregate count, rather a sequence no within each BY group.

Can't see why you wish to store equal values in four new columns, what can be the requirement for this?

But to me, just do the divide and assignment in the same step as creating the "count"....?

Data never sleeps
Onyx | Level 15

## Re: Count the number of Observation by variable and then divide them by 4 and create new coloumn

One solution,

data test;
input spool_number \$ dose number batch \$;
datalines;
su 30 1 rx1
su 30 2 rx1
su 30 3 rx1
su 30 4 rx1
1 30 5 rx1
2 30 6 rx1
sp 30 7 rx1
su 30 8 rx1
4 30 9 rx1
5 30 10 rx1
6 30 11 rx1
su 14 12 rx1
su 14 13 rx1
su 14 14 rx1
su 14 15 rx1
1 14 16 rx1
2 14 17 rx1
3 14 18 rx1
su 14 19 rx1
4 14 20 rx1
5 14 21 rx1
failure 14 22 rx1
su 14 23 rx1
su 14 24 rx1
su 14 25 rx1
1 14 26 rx1
2 14 27 rx1
su 30 1 rx2
su 30 2 rx2
su 30 3 rx2
su 30 4 rx2
1 30 5 rx2
2 30 6 rx2
sp 30 7 rx2
su 30 8 rx2
4 30 9 rx2
5 30 10 rx2
6 30 11 rx2
su 14 12 rx2
su 14 13 rx2
su 14 14 rx2
su 14 15 rx2
1 14 16 rx2
2 14 17 rx2
3 14 18 rx2
su 14 19 rx2
4 14 20 rx2
5 14 21 rx2
failure 14 22 rx2
su 14 23 rx2
su 14 24 rx2
su 14 25 rx2
1 14 26 rx2
2 14 27 rx2
run;

data want;
do _n_=1 by 1 until (last.batch);
set test;
by batch notsorted;
new_var=cats('q',ifn(mod(_n_,4)=0,4,mod(_n_,4)));
output;
end;
run;

Obsidian | Level 7

## Re: Count the number of Observation by variable and then divide them by 4 and create new coloumn

I Think I didn't explain correctly so output i want will be

spool dose number batch quarter

su 30 1 rx1 1

su 30 2 rx1 1

su 30 3 rx1 1

su 30 4 rx1 1

1 30 5 rx1 1

2 30 6 rx1 1

sp 30 7 rx1 2

su 30 8 rx1 2

4 30 9 rx1 2

5 30 10 rx1 2

6 30 11 rx1 2

su 14 12 rx1 2

su 14 13 rx1 3

su 14 14 rx1 3

su 14 15 rx1 3

1 14 16 rx1 3

2 14 17 rx1 3

3 14 18 rx1 3

su 14 19 rx1 4

4 14 20 rx1 4

5 14 21 rx1 4

failure 14 22 rx1  4

su 14 23 rx1 4

su 14 24 rx1  4

su 14 25 rx1  4

1 14 26 rx1  4

2 14 27 rx1 4

and so on for batch rx2

Onyx | Level 15

## Re: Count the number of Observation by variable and then divide them by 4 and create new coloumn

Not sure if you want the 'q' or not, here is the version with 'q', it gets simpler without 'q',

data want;
do _n_=1 by 1 until (last.batch);
set test;
by batch notsorted;
end;
_cyc=ceil(_n_/4);
do _n_=1 by 1 until (last.batch);
set test;
by batch notsorted;
new_var=cats('q', ceil(_n_/_cyc));
output;
end;
drop _cyc;
run;
Discussion stats
• 5 replies
• 2137 views
• 0 likes
• 4 in conversation