DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

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

[ Edited ]

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

Accepted Solutions
Solution
‎03-28-2017 12:41 PM
Super User
Posts: 10,044

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

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;

View solution in original post


All Replies
PROC Star
Posts: 7,492

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

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

 

Solution
‎03-28-2017 12:41 PM
Super User
Posts: 10,044

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

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;
Frequent Contributor
Posts: 84

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 140 views
  • 1 like
  • 3 in conversation