Hello Guys
i thnk its little tricky.. i have a dataset like this segment, iD and group. i want a macro that loops though each segments and picks 6 rows(2 rows from group w , 3 rows from b,1 rows from m) this 6 parameter should be dynamic parameters, in future if i need to change it 5 or 7 or whaterver obs from each group i can easily change so please note this. and for next run again 6 rows if the groups id exhausted pick from other group to make it 6 rows and so on..and this is for multiple runs..i hope i explined the requirement if not do not hesitate to comment. Thanks
Segement | ID | group |
1 | 101 | w |
1 | 102 | w |
1 | 103 | w |
1 | 105 | b |
1 | 106 | b |
1 | 107 | b |
1 | 108 | b |
1 | 109 | b |
1 | 110 | b |
1 | 111 | m |
1 | 112 | m |
3 | 115 | w |
1 | 116 | w |
3 | 117 | b |
3 | 118 | b |
3 | 119 | m |
output for only Segment 1:
1st run 6 records | 2nd run 6 records | 3rd run 6 records | 4th run records | |||||
from W 2 IDs | 101 | from W remianing 1 IDs | 103 | 102 | 101 | |||
102 | from B 3 remaining 3 IDs | 108 | 103 | 102 | ||||
from B 3 IDs | 105 | 109 | 105 | 108 | ||||
106 | 110 | 106 | 109 | |||||
107 | from m remianing 1 ID | 112 | 107 | 110 | ||||
from m 1 IDs | 111 | from w to q to make 6 | 101 | 111 | 112 |
sample output for Segment 3:
1st run 6 records | 2nd run | ||
2 from w | 115 | 116 | |
116 | 117 | ||
2 from b | 117 | 118 | |
118 | 119 | ||
1 from m | 119 | 115 | |
1 from w | 115 | 116 |
It is very intesting question.
How about this one.
data have;
infile cards expandtabs truncover;
input Segement ID group $;
cards;
1 101 w
1 102 w
1 103 w
1 105 b
1 106 b
1 107 b
1 108 b
1 109 b
1 110 b
1 111 m
1 112 m
3 115 w
3 116 w
3 117 b
3 118 b
3 119 m
;
run;
%let times=3;
%let w=2;
%let b=3;
%let m=1;
proc sort data=have;
by Segement group;
run;
data want;
set have;
by Segement group;
array w{9999} _temporary_;
array b{9999} _temporary_;
array m{9999} _temporary_;
if first.segement then call missing(of w{*},of b{*},of m{*});
if first.group then n=0;
n+1;
if group='w' then w{n}=id;
else if group='b' then b{n}=id;
else if group='m' then m{n}=id;
if last.group then do;
if group='w' then do;
mm=0;
do time=1 to ×
do i=1 to &w;
mm+1;
idx=mod(mm,n);
if idx=0 then idx=n;
id=w{idx};
output;
end;
end;
end;
else if group='b' then do;
mm=0;
do time=1 to ×
do i=1 to &b;
mm+1;
idx=mod(mm,n);
if idx=0 then idx=n;
id=b{idx};
output;
end;
end;
end;
else if group='m' then do;
mm=0;
do time=1 to ×
do i=1 to &m;
mm+1;
idx=mod(mm,n);
if idx=0 then idx=n;
id=m{idx};
output;
end;
end;
end;
end;
drop n mm i idx;
run;
proc sort data=want;
by time;
run;
proc print noobs;run;
I don't understand your replacement logic but, not dealing with the replacments, the following should at least get you started:
%macro doit(w=2,b=3,m=1); data run1 (drop=counter total) remaining1 (drop=counter total); set have; by segement group notsorted; if group eq 'w' then total=&w.; else if group eq 'b' then total=&b.; else if group eq 'm' then total=&m.; if first.group then counter=0; if counter lt total then do; counter+1; output run1; end; else output remaining1; run; data run2 (drop=counter total) remaining2 (drop=counter total); set remaining1; by segement group notsorted; if group eq 'w' then total=&w.; else if group eq 'b' then total=&b.; else if group eq 'm' then total=&m.; if first.group then counter=0; if counter lt total then do; counter+1; output run2; end; else output remaining2; run; %mend doit; %doit()
Art, CEO, AnalystFinder.com
It is very intesting question.
How about this one.
data have;
infile cards expandtabs truncover;
input Segement ID group $;
cards;
1 101 w
1 102 w
1 103 w
1 105 b
1 106 b
1 107 b
1 108 b
1 109 b
1 110 b
1 111 m
1 112 m
3 115 w
3 116 w
3 117 b
3 118 b
3 119 m
;
run;
%let times=3;
%let w=2;
%let b=3;
%let m=1;
proc sort data=have;
by Segement group;
run;
data want;
set have;
by Segement group;
array w{9999} _temporary_;
array b{9999} _temporary_;
array m{9999} _temporary_;
if first.segement then call missing(of w{*},of b{*},of m{*});
if first.group then n=0;
n+1;
if group='w' then w{n}=id;
else if group='b' then b{n}=id;
else if group='m' then m{n}=id;
if last.group then do;
if group='w' then do;
mm=0;
do time=1 to ×
do i=1 to &w;
mm+1;
idx=mod(mm,n);
if idx=0 then idx=n;
id=w{idx};
output;
end;
end;
end;
else if group='b' then do;
mm=0;
do time=1 to ×
do i=1 to &b;
mm+1;
idx=mod(mm,n);
if idx=0 then idx=n;
id=b{idx};
output;
end;
end;
end;
else if group='m' then do;
mm=0;
do time=1 to ×
do i=1 to &m;
mm+1;
idx=mod(mm,n);
if idx=0 then idx=n;
id=m{idx};
output;
end;
end;
end;
end;
drop n mm i idx;
run;
proc sort data=want;
by time;
run;
proc print noobs;run;
Excellent! Thanks for your time. you are the best.
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.