topic Re: Pick the IDs from each group to make it fixed obs and loop through each group until meet the obs in SAS Programming

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

kumarK — Mon, 27 Mar 2017 18:23:26 GMT

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

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

art297 — Mon, 27 Mar 2017 19:59:18 GMT

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

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

Ksharp — Tue, 28 Mar 2017 05:21:49 GMT

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 &times;
     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 &times;
     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 &times;
     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;

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

kumarK — Tue, 28 Mar 2017 16:41:55 GMT

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