Combining rows of data

[Ksharp]

Here is my IML code. If your compute was good eough , change the following parameter as big as you can to make sure find the optimal solution.

niter = 10000;

NOTE: I  highly strong recommend you to post this question at OR forum, Maybe there is some OR code offered by @RobPratt  you can use , due to the fact GA can't guarantee you get the optimal solution for the sake of large scale problem.

data have;
infile cards expandtabs truncover;
input TableList : $20. PlayerList  : $20. ScoreA	ScoreB	ScoreC	ScoreD;
cards;
A_C_P_AC	12	4	8	12	16
R_AA_AB	17	3	6	9	12
B_F_G_K_L_O	3,4,11	18	36	54	72
S_T_U_V_AD	5,6	10	20	30	40
D_E_H_I_J_M_N	1,2,14	21	42	63	84
Y,Z	13,16	4	8	12	16
W,X	7,15	4	8	12	16
Q	8,9,10	3	6	9	12
;
run;



proc iml;
use have;
read all var _num_ into age[c=vname] ;
close ;

start function(x) global(age,group,nrow,ncol);
sum_group=j(group,ncol,0);
do i=1 to group;
 do j=1 to nrow;
  if x[j]=i then sum_group[i,]=sum_group[i,]+age[j,];
 end;
end;
obj=sum(sum_group[<>,]-sum_group[><,]);
return (obj);
finish;

start switch_mut(s) global(nswitches);
  n = ncol(s);
  do i = 1 to nswitches;
   k1 = int(uniform(123456789)*n) + 1;
   k2 = int(uniform(123456789)*n) + 1;
   if k1^=k2 then do;
    temp=s[k2];
    s[k2] = s[k1];
    s[k1] = temp;
   end;
  end;
finish;

start uniform_cross(child1, child2, parent1, parent2) global(nrow);
child1 = parent1;
child2 = parent2;
do i = 1 to ncol(parent1);
  r = uniform(123456789);
  if r<0.5 then do;
   child1[i] = parent2[i];
   child2[i] = parent1[i];
  end;
end;
finish;


ncol=ncol(age);
nrow=nrow(age);
obs=t(1:nrow);
group=4;  /* <--Change it(divide into 4 groups)*/


nswitches = 4;
encoding=j(2,nrow,1);
encoding[2,]=group;    

id=gasetup(2,nrow,123456789);
call gasetobj(id,0,"function");
call gasetcro(id,0.95,0,"uniform_cross");
call gasetmut(id,0.95,0,"switch_mut");
call gasetsel(id,100,1,1);
call gainit(id,1000,encoding);


niter = 10000;
do i = 1 to niter;
 call garegen(id);
 call gagetval(value, id);
end;
call gagetmem(mem, value, id, 1);

groups=t(mem);
create group var {groups};
append;
close;
print value[l = "Min Value:"] ;
call gaend(id);
quit;
 

data want;
 merge group have;
run;
proc summary data=want nway;
 class groups;
 var _numeric_;
 output out=sum(drop=_:) sum=;
run;
proc print noobs;run;
proc print data=want noobs;run;

[Ksharp]

This one could be faster. Same result with previous .

data have;
infile cards expandtabs truncover;
input TableList : $20. PlayerList  : $20. ScoreA	ScoreB	ScoreC	ScoreD;
cards;
A_C_P_AC	12	4	8	12	16
R_AA_AB	17	3	6	9	12
B_F_G_K_L_O	3,4,11	18	36	54	72
S_T_U_V_AD	5,6	10	20	30	40
D_E_H_I_J_M_N	1,2,14	21	42	63	84
Y,Z	13,16	4	8	12	16
W,X	7,15	4	8	12	16
Q	8,9,10	3	6	9	12
;
run;



proc iml;
use have;
read all var _num_ into age[c=vname] ;
close ;

start function(x) global(age,group,nrow,ncol);
sum_group=j(group,ncol,0);
do i=1 to group;
 do j=1 to nrow;
  if x[j]=i then sum_group[i,]=sum_group[i,]+age[j,];
 end;
end;
obj=sum(sum_group[<>,]-sum_group[><,]);
return (obj);
finish;

ncol=ncol(age);
nrow=nrow(age);
group=4;  /* <--Change it(divide into 4 groups)*/

encoding=j(2,nrow,1);
encoding[2,]=group;    

id=gasetup(2,nrow,123456789);
call gasetobj(id,0,"function");
call gasetsel(id,100,1,1);
call gainit(id,1000,encoding);


niter = 10000;
do i = 1 to niter;
 call garegen(id);
 call gagetval(value, id);
end;
call gagetmem(mem, value, id, 1);

groups=t(mem);
create group var {groups};
append;
close;
print value[l = "Min Value:"] ;
call gaend(id);
quit;
 

data want;
 merge group have;
run;
proc summary data=want nway ;
 class groups;
 var _numeric_;
 output out=sum(drop=_:) sum=;
run;
proc print noobs;run;
proc print data=want noobs;run;

[Krislynn742005]

Hi KSharp,

I ran the code you sent and it works perfectly on the small sample dataset.  I applied it to the the actual larger dataset and after 24 hours of contiuous running it still wasn't finished.  Is there anything I can do that will make this run faster???

Thanks,
Krislynn

[Ksharp]

Can you post it at 

https://communities.sas.com/t5/Mathematical-Optimization/OR-Challenge-again/m-p/274991

@RobPratt is there , He maybe give you fast way.

About reducing time , you can change   niter=10000 into a small number , 

But I don't recommend to do that because you may not find a best solution.

I advice to wait .

[Ksharp]

Remove this line call gasetsel(id,100,1,1); may get you faster .

[Krislynn742005]

Before removing the code as suggested, I tried running it one more time and
this is my log. As you can see it is giving me an error in the section
where you said I should remove the line of code. I get the same error when
I remove the code or leave that line in. Advice????

proc iml;

NOTE: Writing HTML Body file: sashtml1.htm

NOTE: IML Ready

96 use out.FinalAssignwithSamples;

97 read all var _num_ into age[c=vname] ;

98 close ;

NOTE: Closing OUT.FINALASSIGNWITHSAMPLES

99

100 start function(x) global(age,group,nrow,ncol);

101 sum_group=j(group,ncol,0);

102 do i=1 to group;

103 do j=1 to nrow;

104 if x[j]=i then sum_group[i,]=sum_group[i,]+age[j,];

105 end;

106 end;

107 obj=sum(sum_group[<>,]-sum_group[><,]);

108 return (obj);

109 finish;

NOTE: Module FUNCTION defined.

110

111 start switch_mut(s) global(nswitches);

112 n = ncol(s);

113 do i = 1 to nswitches;

114 k1 = int(uniform(123456789)*n) + 1;

115 k2 = int(uniform(123456789)*n) + 1;

116 if k1^=k2 then do;

117 temp=s[k2];

118 s[k2] = s[k1];

119 s[k1] = temp;

120 end;

121 end;

122 finish;

NOTE: Module SWITCH_MUT defined.

123

124 start uniform_cross(child1, child2, parent1, parent2) global(nrow);

125 child1 = parent1;

126 child2 = parent2;

127 do i = 1 to ncol(parent1);

128 r = uniform(123456789);

129 if r<0.5 then do;

130 child1[i] = parent2[i];

131 child2[i] = parent1[i];

132 end;

133 end;

134 finish;

NOTE: Module UNIFORM_CROSS defined.

135

136

137 ncol=ncol(age);

138 nrow=nrow(age);

139 obs=t(1:nrow);

140 group=4;

140! /* <--Change it(divide into 4 groups)*/

141

142

143 nswitches = 4;

144 encoding=j(2,nrow,1);

145 encoding[2,]=group;

146

147 id=gasetup(2,nrow,123456789);

148 call gasetobj(id,0,"function");

149 call gasetcro(id,0.95,0,"uniform_cross");

150 call gasetmut(id,0.95,0,"switch_mut");

151 call gasetsel(id,100,1,1);

152 call gainit(id,1000,encoding);

ERROR: Overflow error in -.



operation : - at line 107 column 23

operands : _TEM1001, _TEM1002

_TEM1001 1 row 84 cols (numeric)

_TEM1002 1 row 84 cols (numeric)



statement : ASSIGN at line 107 column 1

traceback : module FUNCTION at line 107 column 1





operation : GAINIT at line 152 column 1

operands : id, *LIT1031, encoding



id 1 row 1 col (numeric)



0



*LIT1031 1 row 1 col (numeric)



1000

encoding 2 rows 1286 cols (numeric)



statement : CALL at line 152 column 1

153

154

155 niter = 10000;

156 do i = 1 to niter;

157 call garegen(id);

158 call gagetval(value, id);

159 end;

ERROR: Genetic Algorithm must be initialized with CALL GAINIT

ERROR: Execution error as noted previously. (rc=1000)



operation : GAREGEN at line 157 column 1

operands : id



id 1 row 1 col (numeric)



0



statement : CALL at line 157 column 1



statement : CALL at line 157 column 1

160 call gagetmem(mem, value, id, 1);

ERROR: Genetic Algorithm must be initialized with CALL GAINIT

ERROR: Execution error as noted previously. (rc=1000)



operation : GAGETMEM at line 160 column 1

operands : id, *LIT1034



id 1 row 1 col (numeric)



0



*LIT1034 1 row 1 col (numeric)



1



statement : CALL at line 160 column 1



statement : CALL at line 160 column 1

161

162 groups=t(mem);

ERROR: (execution) Matrix has not been set to a value.



operation : T at line 162 column 9

operands : mem



mem 0 row 0 col (type ?, size 0)





statement : ASSIGN at line 162 column 1

163 create group var {groups};

164 append;

WARNING: All data set variables are unvalued. No APPEND done.



statement : APPEND at line 164 column 1

165 close;

NOTE: Closing WORK.GROUP

NOTE: The data set WORK.GROUP has 0 observations and 1 variables.

166 print value[l = "Min Value:"] ;

ERROR: Matrix value has not been set to a value.



statement : PRINT at line 166 column 1

167 call gaend(id);

168 quit;

NOTE: Exiting IML.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IML used (Total process time):

real time 12.58 seconds

cpu time 1.23 seconds



169

170





171 data out.FinalAssignwithSamples2;

172 merge group out.FinalAssignwithSamples;

173 run;



NOTE: There were 0 observations read from the data set WORK.GROUP.

NOTE: There were 1286 observations read from the data set
OUT.FINALASSIGNWITHSAMPLES.

NOTE: The data set OUT.FINALASSIGNWITHSAMPLES2 has 1286 observations and 87
variables.

NOTE: DATA statement used (Total process time):

real time 1.99 seconds

cpu time 1.60 seconds





174 proc summary data=out.FinalAssignwithSamples2 nway;

175 class groups;

176 var _numeric_;

177 output out=sum(drop=_:) sum=;

178 run;



WARNING: Variable GROUPS already exists on file WORK.SUM.

WARNING: The duplicate variables will not be included in the output data set
of the output

statement number 1.

WARNING: A class or frequency variable is missing on every observation.

NOTE: There were 1286 observations read from the data set
OUT.FINALASSIGNWITHSAMPLES2.

NOTE: The data set WORK.SUM has 0 observations and 85 variables.

NOTE: PROCEDURE SUMMARY used (Total process time):

real time 0.93 seconds

cpu time 0.78 seconds





179 proc print noobs;run;



NOTE: No observations in data set WORK.SUM.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds





180 proc print data=out.FinalAssignwithSamples2 noobs;run;



NOTE: There were 1286 observations read from the data set
OUT.FINALASSIGNWITHSAMPLES2.

NOTE: PROCEDURE PRINT used (Total process time):

real time 16.22 seconds

cpu time 14.89 seconds







##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Ksharp]

That is really odd. 

Can you start a brand new sas session and run the code again ?

I would suggest retain that statement .

If you could accept OR code , I really suggest you post it at OR forum , and let  Rob know .

[Krislynn742005]

I'm still getting the overflow error when I run the code on my actual data
set. It runs perfectly on the small sample set but not the large real data
set. Is there any way to fix this? I can't use OR because we don't have
that module.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Ksharp]

How many obs do you have ? Maybe your number is too big to pass the limit of SAS.

Try following:

P.S. Better post some sample data to let us test it .

call gasetsel(id,10,1,1); /*10 <-- Change it smaller as possible*/
call gainit(id,100,encoding); /*10 <-- Change it smaller as possible


niter = 10000; /*<-- Change it bigger as possible to make sure find a optimal solution*/

[Ksharp]

Can you set the option
-memsize 10G
in the sas config file   SASCFGV9  
to make your memory size as bigger as it could be ?

[Krislynn742005]

I can't even find that file.

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[Ksharp]

[Ksharp]

OR Try this way:

After that you can check how much memory SAS have already used .

[Ksharp]

@RobPratt offer the OR code , Hope you can run it either. https://communities.sas.com/t5/Mathematical-Optimization/OR-Challenge-again/m-p/274991

[Ksharp]

Can you explain where WEEK come from ?

Why some TableList have the same week ?

TableList	PlayerList	ScoreA	ScoreB	ScoreC	ScoreD	Total Samples	Week
A_C_P_AC	12	4	8	12	16	40	Week2
R_AA_AB	17	3	6	9	12	30	Week2
B_F_G_K_L_O	3,4,11	18	36	54	72	180	Week1
S_T_U_V_AD	5,6	10	20	30	40	100	Week2
D_E_H_I_J_M_N	1,2,14	21	42	63	84	210	Week3
Y,Z	13,16	4	8	12	16	40	Week4
W,X	7,15	4	8	12	16	40	Week4
Q	8,9,10	3	6	9	12	30	Week4