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?
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;
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.
"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);
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?
Yeah. Start a brand new session ,let more sas users to see it .
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.