Pick the IDs from each group to make it fixed obs and loop through each group until meet the obs

[kumarK]

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

[art297]

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

[Ksharp · Accepted solution]

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;

[kumarK]

Excellent! Thanks for your time. you are the best.