DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor dpa
Contributor
Posts: 35
Accepted Solution

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


Accepted Solutions
Solution
‎08-30-2016 10:40 AM
Respected Advisor
Posts: 3,124

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

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

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;
Super User
Posts: 5,255

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
Respected Advisor
Posts: 3,124

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;

Contributor dpa
Contributor
Posts: 35

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

Solution
‎08-30-2016 10:40 AM
Respected Advisor
Posts: 3,124

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 262 views
  • 0 likes
  • 4 in conversation