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
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;
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;
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"....?
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.