Do you consider to use In_date and Out_date as grouping variable too ? If you only consider " start & end time and the room number" ,that might be meet a error . Anyway , Anyhow , that is really not easy for all the combination. data x;
input client_id $ Est_Amount Max_Play Avg_Play in_date : mmddyy10. out_date : mmddyy10.
Start_Time & time11. End_Time & time12. timeplayed Room_Number ;
format In_Date Out_Date Date9. Start_Time End_Time time8.;
datalines;
P1 33000 20000 5000 3/7/2012 3/7/2012 04:17:00 PM 06:34:00 PM 137 4444
P2 -30000 7000 2000 3/7/2012 3/7/2012 04:31:00 PM 06:16:00 PM 105 4444
P1 -233000 30000 15000 3/7/2012 3/7/2012 06:34:00 PM 07:55:00 PM 81 4444
P3 -50000 50000 50000 3/7/2012 3/7/2012 06:59:00 PM 07:00:00 PM 1 4444
P1 -47000 20000 10000 3/7/2012 3/7/2012 09:05:00 PM 09:48:00 PM 43 6789
P2 42000 25000 10000 3/7/2012 3/7/2012 09:24:00 PM 10:49:00 PM 85 6789
P4 8000 21000 8000 3/7/2012 3/7/2012 09:38:00 PM 10:55:00 PM 77 6789
;
run;
data one;
set x ;
if (Room_Number=lag(Room_Number) and lag(End_Time) lt Start_Time) or (Room_Number ne lag(Room_Number)) then group+1;
run;
proc transpose data=one out=temp(drop=_name_) ;
by group;
id client_id;
var Est_Amount;
run;
data two(keep=group var);
set temp;
length var $ 200;
array x{*} p: ;
n=dim(x);
do k=2 to n;
ncomb=comb(n,k);
do j=1 to ncomb;
call allcomb(j, k, of x{*});
flag=0;sum=0;var=' ';
do i=1 to k;
if missing(x{i}) then flag=1;
end;
if not flag then do;
do i=1 to k;
sum+x{i};
end;
if abs(sum) le 5000 then do;
do i=1 to k;
var=catx(',',var,x{i});
end;
output;
end;
end;
end;
end;
run;
data two(keep=group var _var);
merge two(in=a) temp;
by group;
length _var $ 200;
array x{*} p: ;
if a then do;
do j=1 to countw(var,',');
temp=input(scan(var,j,','),best32.);
do i=1 to dim(x);
if temp eq x{i} and not findw(_var,strip(vname(x{i}))) then _var=catx(',',_var,vname(x{i}));
end;
end;
output;
end;
run;
Ksharp
... View more
