Partition by equivalent in SAS base or proc sql

[jovic92]

Error : Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero, or invalid.

NOTE: Missing values were generated as a result of performing an operation on missing values. 

I have a few rows of missing values in Usage and Time columns in this dataset(whole dataset has about milion rows), I assume that causes the problem?

[Ksharp]

Yeah. That is saying you have missing value for TIME variable ,just add one more condition.

data want;
 if _n_=1 then do;
  if 0 then set have(rename=(usage=_usage));
  declare hash h(dataset:'have(where=(time is not missing) rename=(usage=_usage))',hashexp:20);
  h.definekey('user','id_option','time');
  h.definedata('_usage');
  h.definedone();
 end;
set have;
sum_usage_3hr=0;

if not missing(time) then do;
do i=time-3*3600 to time ;
 if h.find(key:user,key:id_option,key:i)=0 then sum_usage_3hr+_usage;
end;
end;

drop _usage i;
run;

[jovic92]

Now it's all zeros in SUM_USAGE_3HR. Can it be a problem if it's TIME stored as a string or as a integer? 

When I wrote a program for loading data (after that, and before this step I had a few filtering and join) in input I include: TIME anydtdtm23.

[Ksharp]

"Now it's all zeros in SUM_USAGE_3HR. Can it be a problem if it's TIME stored as a string or as a integer? "

I doubted your TIME is not real integer, it should have decimal number.

Therefore Hash Table can correctly find the right TIME.

use int() to make TIME is integer as I said before.

TIME=int(TIME);

[jovic92]

Thank you very much. I've solved this problem using the first code snippet you posted. Now I have just one, pretty similar problem, but I don't know did I need to create a new topic? 

[Ksharp]

Yeah. Start a brand new session ,let more sas users to see it .

[s_lassen]

@jovic92: If your data is sorted, you can use the SET statement with POINT= to calculate rolling sums:

proc sort data=have;
  by User ID_option time;
run;

data want;
  set have;
  by User ID_option ;
  if first.ID_option then do;
    _Pstart=_N_;
    _start=time;
    sum=usage;
    end;
  else do;
    sum+usage;
    do _Pstart=_Pstart to _N_;
      set have(keep=time usage rename=(time=_start usage=_subtract)) point=_Pstart;
      if _start>=intnx('second', time, -3*3600) then
        leave;
      sum+-_subtract;
      end;
    end;
  drop _:;
run;