LB and UB are intended to be in number form. Since ARRAY statements do not accept date literals as array bounds, you have to convert SAS dates into corresponding integer values.
But it turns out the error is a missing semi-colon prior to the ARRAY statement - 95% of my error are missing semi-colons.
hi mkeintz,
I ran the code and got the following error message:
243 %let LB=%sysfunc(intnx(year,%sysfunc(inputn(&begdate,date9.)),-4,sameday));
244 %let UB=%sysfunc(inputn(&enddate,date9.));
245 data want (drop=_:);
246 set have (keep=id date x);
247 by id;
248
249 retain _rolling_ss /* Rolling sum of squares*/
250 _rolling_sum /* Rolling sum */
251 _rolling_n /* Rolling count */ ;
252
253 array history {&lb:&ub} _temporary_;
254
255 if first.id then call missing(of history{*});
256
257 history{date}=x;
258
259 if x^=. then do;
260 _rolling_n+ 1;
261 _rolling_sum+ x;
262 _rolling_ss+ x**2;
263 end;
264
265 _most_recent_drop_date=intnx('year',date,-4,'sameday');
266 _prior_most_recent_dd=lag(_most_recent_drop_date);
267
268 if first.id=0 then do _drop_date=_prior_most_recent_dd+1 to _most_recent_drop_date;
269 if history{_drop_date} ^= . then do;
270 _rolling_n+ (-1);
271 _rolling_sum+ (-history{_drop_date});
272 _rolling_ss+ (-history{_drop_date}**2);
273 end;
274 end;
275
276 _rolling_mean=_rolling_sum/_rolling_n;
277 rolling_std=sqrt( ( _rolling_ss - _rolling_n*(_rolling_mean**2))/(_rolling_n-1)
277! );
278 run;
NOTE: Division by zero detected at line 277 column 68.
DATE=02JAN1997 id=85208J10 x=4.625 FIRST.id=1 LAST.id=0 _rolling_ss=21.390625 _rolling_sum=4.625
_rolling_n=1 _most_recent_drop_date=12055 _prior_most_recent_dd=. _drop_date=. _rolling_mean=4.625
rolling_std=. _ERROR_=1 _N_=1
ERROR: Array subscript out of range at line 269 column 8.
DATE=03JAN1997 id=85208J10 x=4.59375 FIRST.id=0 LAST.id=0 _rolling_ss=42.493164063
_rolling_sum=9.21875 _rolling_n=2 _most_recent_drop_date=12056 _prior_most_recent_dd=12055
_drop_date=12056 _rolling_mean=. rolling_std=. _ERROR_=1 _N_=2
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 277:15
NOTE: Mathematical operations could not be performed at the following places. The results of the
operations have been set to missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 277:68
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 3 observations read from the data set WORK.HAVE.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 1
observations and 4 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.03 seconds
Now there is only one error message: the array subscript is out of range, and in some places it says that there was a division by zero, so I don't know to what extent it has an influence.
At the end got the table want having only 1 observation, with stdev being missing.
Thnaks!
I said the code was untested, and you're revealing why.
Everything you need to know is in the log.
Division by zero. This program generates rolling STD for up-to-four-years of daily data, and does not set a minimum window size. As a result it attempts to compute an STD even when you are starting a new id, i.e. when the window has only one observation (_rolling_n=1). Since line 277 has (_rolling_n-1) in the denominator you'r dividing be zero. You might want to generate an IF test to require a minimum _rolling_n value in line 277.
The array subscript out of bounds occurs at _N_=2. This means _drop_date is either below the lower bound or above the upper bound of your array. So look at the value of drop_date, figure out what it is as a sas date value, and determine its relation to macrovar LB and UB.
I did %let begdate=02jan1997; since it is the earliest date in the sample. I ontained the stdevs for all the observations and exported to Excel to to manual verifications and observed the following: For the first id I obtain the same stdevs as the ones imported from SAS, but when the second id begins, it's stdev calculation takes into account the x's from the previous id - but why does this happen if in the code did if first.id then call missing(of history{*}); ?
Make sure, that at first.id, all the relevant elements are reset to missing, not just the history array.
You can safely use BEGDATE = 02JAN2001 (i.e. the earliest actual data record), because the lower bound macrovar LB is set to 4 years prior, so any 4-year lookback will not be out of range.
As to your question, you will have to examine the cases with a difference, and see whether the start of the date range for the non-matching STD's are the same. If not, develop a rule to match them. I presume the end dates will match.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: