Tricky array problem

[SPhill]

Hello, in my base table below I'm trying to create a rolling summary for unitt(i), by polref innref covref fund(i) The base array data is 'table 1' my output is 'table 2'. However with my code below I lose my rolling summary for fund2 due to it being set at fund1 on line 71 which gives obs 5 a unitt2 of 540.324 in table 2 when it should be 10. I've tried fund(i) in line 71 which doesn't work.

Any suggestions how to get this to work.

Thanks
SP
(sorry it's so small, I couldn't get it to fit in normal size...the tables paste into excel dlmtd for easier viewing)

[small]
                         Table1
Obs	polref	inrref	covref	fund1	unit1	fund2	unit2

1	90050753	1	1	333	0.432	.	.
2	90050753	1	1	333	531.571	336	350.255
3	90050753	1	1	333	528.214	336	342.155
4	90050753	2	1	333	807.325	336	530.324
5	90050753	2	1	333	5	337	10
6	90050753	3	1	333	14.46	336	9.62
7	90050753	3	1	333	15.769	336	10.333
8	90050753	4	1	333	453.088	336	310.076
9	90050753	4	1	333	201.728	336	143.708
                         Table 2
Obs	polref	inrref	covref	fund1	unitt1	fund2	unitt2

1	90050753	1	1	333	0.43	.	0
2	90050753	1	1	333	532	336	350.255
3	90050753	1	1	333	1060.22	336	692.41
4	90050753	2	1	333	807.32	336	530.324
5	90050753	2	1	333	812.32	337	540.324
6	90050753	3	1	333	14.46	336	9.62
7	90050753	3	1	333	30.23	336	19.952
8	90050753	4	1	333	453.09	336	310.076
9	90050753	4	1	333	654.82	336	453.784

my code below:-
64 DATA temp;                                                              
65 SET a1;                                                                 
66 ARRAY unitt(20) unitt1-unitt20;                                         
67 ARRAY unit(20) unit1-unit20;                                            
68 BY polref inrref covref fund1 fund2 fund3 fund4 fund5 fund6 fund7 fund8 
69    fund9 fund10 fund11 fund12 fund13 fund14 fund15 fund16 fund17 fund18 
70    fund19 fund20;                                                       
71 IF FIRST.fund1 THEN DO i=1 TO dim(unitt);                               
72 unitt(i)=0;                                                             
73 END;                                                                    
74 *;                                                                      
75  DO i=1 TO dim(unitt);                                                  
76   unitt(i)+unit(i);                                                     
77  END;                                                                   
                                         

[/small]

 

[klaz2002]

Your logic is good. Its your data that gets you. You use the FIRST. keyword and your data is sorted by the vars that you listed ( too many to relist here) I notice that in your data the column 'inrref' has a two in obs 4, well that would make the FIRST.fund1 in line 72 return a 1 or true, and then you get the reset logic that you worked into your code.

I can't really help you in deciding which vars to sort by as only you or your team can decide that. What you need to do is sit down and decide how and what makes the BY group. Remember a BY group should be a unique KEY to access your data quickly via indexing. Yes, I know a million people will contest this last statement, but while we use the BY grouping all over SAS for reports etc the real use for it is faster data access via subsetting data etc.

Ok I have vented enough today, think of the minimum BY vars you need then using your existing code you will get a running total for your by group.

Hope this helps you.
Klaz