cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
eroka
‎11-13-2020
  • 7 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Creating a 5 month rolling average - vertically or horizontally (o...

by Calcite | Level 5 in SAS Programming
‎09-18-2018 07:14 AM
‎09-18-2018 07:14 AM
Dear MKeintz,   It doesn't work. The error that I get is  ERROR: Array subscript out of range at line 39 column 41.   If I break the code into two and run the first part:   data Want (drop=sum_ix index); set Have ; by region_name notsorted; array ix_mean {2016:2018,1:12} m2016_01-m2016_12 m2017_01-m2017_12 m2018_01-m2018_12 ; retain m20: sum_ix; run;   Then I get a huge data set that has all the date range you created in the columns (see attached). The dataset has 23k records (as many as my observation and then columns as many as you have defined (36 I think).     Once I run the second part:   data WANT_1 ; set WANT; if first.region_name then call missing(sum_ix,of ix_mean{*}); sum_ix + index - coalesce(lag5(index),0); if lag4(region_name)=region_name then ix_mean{year(date),month(date)}=sum_ix/5; if last.region_name; run;   Then I get more specific errors: 44 data WANT_1 47 ; set WANT; 49 if first.region_name then call missing(sum_ix,of ix_mean{*}); ERROR: Undeclared array referenced: ix_mean. ERROR: The ARRAYNAME[*] specification requires an array. 50 sum_ix + index - coalesce(lag5(index),0); 51 52 if lag4(region_name)=region_name then ix_mean{year(date),month(date)}=sum_ix/5; ERROR: Undeclared array referenced: ix_mean. ERROR: Variable ix_mean has not been declared as an array. 53 if last.region_name; 54 run;   I am not familiar with working with arrays to be honest so this issue with "undeclared array" is an odd one for me - as I can see that you have declared it in:   array ix_mean {2016:2018,1:12} m2016_01-m2016_12  m2017_01-m2017_12  m2018_01-m2018_12 ; retain m20: sum_ix;   Any idea what is going wrong...? BTW - my data has no missing observations.   Thank you fro your help!   Edo ... View more

Re: Creating a 5 month rolling average - vertically or horizontally (o...

by Calcite | Level 5 in SAS Programming
‎09-17-2018 01:12 PM
‎09-17-2018 01:12 PM
Hi mkeintz, Thank you for your suggestion! My SAS log returns: 26 data Index_MATRIX_201806_Test_1 (drop=sum_ix); 27 set Index_MATRIX_201806_Test; 28 by region notsorted; 29 if first.region then call missing(sum_ix,of ix_mean{*}); ERROR: Undeclared array referenced: ix_mean. ERROR: The ARRAYNAME[*] specification requires an array. 30 sum_ix + index - coalesce(lag5(index),0); 31 32 array means {2016:2018,1,12} 33 m2016_01-m2016_12 34 m2017_01-m2017_12 35 m2018_01-m2018_12 ; 36 retain m20: ; 37 38 if lag4(region)=region then mean{year(date),month(date)=sum_index/5; _ 388 76 ERROR: Undeclared array referenced: mean. ERROR 388-185: Expecting an arithmetic operator. ERROR 76-322: Syntax error, statement will be ignored. 39 if last.regon; 40 run; Any suggestions around the IX_MEAN and MEAN that are undeclared?... Edo ... View more

Re: Creating a 5 month rolling average - vertically or horizontally (o...

by Calcite | Level 5 in SAS Programming
‎09-17-2018 09:23 AM
‎09-17-2018 09:23 AM
Hi, I have managed to work out the means logic using LAG statement. However I am struggling how to implement it within a loop that is limited to the region (i.e to apply this logic only to the BY group of Region_Name. This I am sure is a fairly simple loop but I am not sure how to loop between By groups. Your help would be greatly appreciated!     The logic I would want SAS to implement within each Region-Name is as follows:   proc sort data = Test_Lag; by Region_Name Index_Month ; Data Test_Lag ; set Test_Lag ; X+1 ; Prev_Rate = Lag (Index); If Prev_Rate = . then Prev_Rate = Index ; Prev2_Rate = Lag2 (Index); If Prev2_Rate = . then Prev2_Rate = Prev_Rate ; proc sort data = Test_Lag; by descending x;   Data Test_Lag ; set Test_Lag ; Next_Rate = Lag (Index); If Next_Rate = . then Next_Rate = Index ; Next2_Rate = Lag2 (Index); If Next2_Rate = . then Next2_Rate = Next_Rate ; proc sort data = Test_Lag; by descending Index_Month; Data Test_Lag ; set Test_Lag ; Mean_5_Months = mean (Prev2_Rate, Prev_Rate, Index, Next_Rate, Next2_Rate ); run; ... View more

Re: Creating a 5 month rolling average - vertically or horizontally (o...

by Calcite | Level 5 in SAS Programming
‎09-17-2018 05:35 AM
‎09-17-2018 05:35 AM
I forgot to add: The Index_Report_Month is the month in which the report is ran, so it is irrelevant for process. It's just informs the reader in which month data the data set was correct for. Every month I get a new data set that one more time point added to it - which happened to be two months before the current month (i.e. in August I get the data correct for June, and The Index Report Month would be "2018-06"). ... View more

Re: Creating a 5 month rolling average - vertically or horizontally (o...

by Calcite | Level 5 in SAS Programming
‎09-17-2018 05:26 AM
‎09-17-2018 05:26 AM
Dear mkeintz,   To answer your questions: 1. The Index Report Month is a string field that I use for each month (format of YYYY-MM) but NOT as a date field. I have found these to be so tricky that it is far better in almost all cases for me to convert the month I am working on to this format. So in this exercise I am relying on Sort as a text - not as a date. Therefore I want to avoid any date calculations as they are really redundant to what I am trying to achieve. I am under the impression that the solution would be using a "set off" point from the MonthX (=Index_Report_Month) - i.e. climbing up and down two cells from the target month observation itself. I am not familiar how that can be done, as it asking to calculate a mean on 5 target points. I hope you guys can advise on this but if you think the solution will benefit from using a date as a date as a hook - I am not against such thinking.   2. I have Enterprise Guide, and I am not familiar with SAS/ets or Proc Expand.   I tried running an example (http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_expand_sect034.htm) but my SAS returned: ERROR: Procedure EXPAND not found.   The transposed version is easier for a human to read and is more compact on screen, allowing to compare the rates in different region.   I do not mind if the solution will be done vertically (before I transpose the data or after, but there is obviously an advantage to the vertical as then I can create two matrices - one with the rolling average and one with the original data.   I hope this is helpful!   ... View more

Re: Creating a 5 month rolling average - vertically or horizontally (o...

by Calcite | Level 5 in SAS Programming
‎09-17-2018 05:09 AM
‎09-17-2018 05:09 AM
That is correct! ... View more

Creating a 5 month rolling average - vertically or horizontally (obser...

by Calcite | Level 5 in SAS Programming
‎09-14-2018 11:39 AM
‎09-14-2018 11:39 AM
I have a data set with 3 variables: 1. Regions - a list of 20 2. Months - the data set has one entry per region per calendar month starting in 2001 3. Index Rate - Each Region each month has one index rate.   Instead of using the index rate per month per region, I need to use instead an average of the index rate from the previous two months and the next two months within the data set. E.g. For the Region London for March 2017 I need to assign that month not the index rate that is applicable for March 2017, but an average number for the index rates I have for Jan, Feb, March, April and May 2017. For April 2017 I would need Feb to June 2017, and so forth. As I get close to the present and I do not have 2 months in the future I will use the last month's index rate one or twice (i.e. for August 2018 I need to use Jule, August and Sept 3 times).   I can do this vertically (i.e. a simple 3 column data set - and then i would need to go up and down the month range) - or if I transpose the data - then with Region on the left column and months as the top headers - it will be horizontally.   The data looks like this:   Date Region_Name Index 01/01/2016 City of London 94.01813825 01/02/2016 City of London 97.70019819 01/03/2016 City of London 101.4148808 01/04/2016 City of London 102.7631184 01/05/2016 City of London 107.900446 01/06/2016 City of London 115.8611285 01/07/2016 City of London 116.3539602 01/08/2016 City of London 108.2436922 01/09/2016 City of London 99.59944053 01/10/2016 City of London 101.1481822 01/11/2016 City of London 104.4657271 01/12/2016 City of London 103.5881838 01/01/2017 City of London 104.6752866 01/02/2017 City of London 104.7212834 01/03/2017 City of London 108.9676798 01/04/2017 City of London 115.6290033 01/05/2017 City of London 119.9377345 01/06/2017 City of London 118.7978333   Or like this: Index_Report_Month Region M2018_06 M2018_05 M2018_04 M2018_03 M2018_02 M2018_01 2018-06 BEDS 136.9514 135.1143 135.4214 135.3575 135.7162 134.7327 2018-06 BERKS 123.3576 122.1503 121.1386 121.7117 121.7369 121.9318 2018-06 BRISTOL 129.4606 128.7055 129.0354 129.9676 130.9927 130.939 2018-06 BUCKS 122.2244 121.4971 121.595 122.5949 124.2265 124.7186 2018-06 CAMBS 124.9302 124.736 123.9808 123.4378 123.2373 123.1942 2018-06 CHESHIRE 112.9974 113.5666 113.6045 112.9975 114.1681 114.6474 2018-06 CORNWALL 114.9373 113.9692 112.5068 113.4169 113.5313 113.1154   Can someone please advise how this can be done? I assume I need to use RETAIN and a macro loop but I am not sure how to do this "moving means", which in excel would be a fairly simple matter.   Much appreciated! Edo ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎11-13-2020 08:07 AM
Group Hubs for eroka
View All
Quick links