BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dpa
Obsidian | Level 7 dpa
Obsidian | Level 7

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
Haikuo
Onyx | Level 15

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
LinusH
Tourmaline | Level 20

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
Haikuo
Onyx | Level 15

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;

dpa
Obsidian | Level 7 dpa
Obsidian | Level 7

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

Haikuo
Onyx | Level 15

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;

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!

How to Concatenate Values

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.

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