topic Re: Count the number of Observation by variable and then divide them by 4 and create new coloumn in SAS Programming

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

dpa — Tue, 30 Aug 2016 13:26:04 GMT

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

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

RW9 — Tue, 30 Aug 2016 13:38:04 GMT

Your test datastep fails. To your question, can you not add in this:

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;

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

LinusH — Tue, 30 Aug 2016 13:38:11 GMT

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"....?

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

Haikuo — Tue, 30 Aug 2016 14:03:11 GMT

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;

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

dpa — Tue, 30 Aug 2016 14:08:58 GMT

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

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

Haikuo — Tue, 30 Aug 2016 14:28:06 GMT

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;