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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.