topic Re: Vertical summation by condition in SAS Programming https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695147#M212077 <P>its vertical summation..not by column</P> Thu, 29 Oct 2020 12:30:54 GMT Cho8 2020-10-29T12:30:54Z Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695143#M212074 <TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Y</TD><TD>d1</TD><TD>d2</TD><TD>d3</TD><TD>d4</TD><TD>d5</TD><TD>d6</TD><TD>d7</TD><TD>d8</TD><TD>d9</TD></TR><TR><TD>201201</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD></TR><TR><TD>201202</TD><TD>0.03</TD><TD>-0.01</TD><TD>0.02</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.04</TD></TR><TR><TD>201203</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.02</TD></TR><TR><TD>201204</TD><TD>0.02</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD></TR><TR><TD>201205</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.03</TD></TR><TR><TD>201206</TD><TD>0.00</TD><TD>0.01</TD><TD>0.01</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD></TR><TR><TD>201207</TD><TD>0.01</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.03</TD><TD>0.00</TD><TD>0.02</TD></TR><TR><TD>201208</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.01</TD><TD>0.00</TD><TD>0.02</TD><TD>0.02</TD><TD>0.00</TD></TR><TR><TD>201209</TD><TD>0.01</TD><TD>0.01</TD><TD>0.00</TD><TD>0.03</TD><TD>0.00</TD><TD>0.02</TD><TD>0.01</TD><TD>0.00</TD><TD>0.02</TD></TR><TR><TD>201210</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.04</TD><TD>0.03</TD><TD>0.02</TD><TD>0.01</TD><TD>0.04</TD></TR><TR><TD>201211</TD><TD>0.01</TD><TD>0.02</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.03</TD><TD>0.00</TD></TR><TR><TD>201212</TD><TD>0.02</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.06</TD><TD>0.00</TD><TD>0.02</TD><TD>0.03</TD></TR><TR><TD>201301</TD><TD>0.02</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.02</TD></TR><TR><TD>201302</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.05</TD></TR><TR><TD>201303</TD><TD>0.01</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD></TR><TR><TD>201304</TD><TD>0.00</TD><TD>0.04</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.03</TD><TD>0.02</TD><TD>0.04</TD><TD>-0.01</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I need average for 12 observation for every row..</P><P>&nbsp;</P><P>for instance.,</P><P>&nbsp;</P><P>for D1..start at row2 to row 14 and average displayed at the bottom of row..</P><P>for D2..start at row3 to row15 and average displayed at bottom of row...</P><P>for D3 start at row4 to row16 and average at bottom of row...</P><P>&nbsp;</P><P>&nbsp;</P><P>for all the variables from D1 until D9..</P> Thu, 29 Oct 2020 12:25:35 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695143#M212074 Cho8 2020-10-29T12:25:35Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695146#M212076 <BLOCKQUOTE> <P><SPAN>I need average for 12 observation for every row.</SPAN></P> </BLOCKQUOTE> <P>&nbsp;</P> <P><SPAN>Please clarify this. Do you really mean "I need average for 12 observation for every <FONT color="#FF0000">column</FONT>."?</SPAN></P> Thu, 29 Oct 2020 12:29:03 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695146#M212076 PaigeMiller 2020-10-29T12:29:03Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695147#M212077 <P>its vertical summation..not by column</P> Thu, 29 Oct 2020 12:30:54 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695147#M212077 Cho8 2020-10-29T12:30:54Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695148#M212078 <P>Do you really want the average to be displayed at the bottom of the row? Can you post exactly what you want the result to look like?</P> Thu, 29 Oct 2020 12:32:33 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695148#M212078 PeterClemmensen 2020-10-29T12:32:33Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695149#M212079 <P>for d1... average of 201202 until201302 at bottom..</P><P>for d2 ...average of 201203 until 201303 at bottom..</P><P>&nbsp;</P><P>and so on..</P><P>&nbsp;</P><P>&nbsp;</P><P>it should be vertical sum..</P> Thu, 29 Oct 2020 12:36:42 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695149#M212079 Cho8 2020-10-29T12:36:42Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695151#M212080 <TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Y</TD><TD>d1</TD><TD>d2</TD><TD>d3</TD><TD>d4</TD><TD>d5</TD><TD>d6</TD><TD>d7</TD><TD>d8</TD><TD>d9</TD></TR><TR><TD>201201</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD></TR><TR><TD>201202</TD><TD>0.03</TD><TD>-0.01</TD><TD>0.02</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.04</TD></TR><TR><TD>201203</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.02</TD></TR><TR><TD>201204</TD><TD>0.02</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD></TR><TR><TD>201205</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.03</TD></TR><TR><TD>201206</TD><TD>0.00</TD><TD>0.01</TD><TD>0.01</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD></TR><TR><TD>201207</TD><TD>0.01</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.03</TD><TD>0.00</TD><TD>0.02</TD></TR><TR><TD>201208</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.01</TD><TD>0.00</TD><TD>0.02</TD><TD>0.02</TD><TD>0.00</TD></TR><TR><TD>201209</TD><TD>0.01</TD><TD>0.01</TD><TD>0.00</TD><TD>0.03</TD><TD>0.00</TD><TD>0.02</TD><TD>0.01</TD><TD>0.00</TD><TD>0.02</TD></TR><TR><TD>201210</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.04</TD><TD>0.03</TD><TD>0.02</TD><TD>0.01</TD><TD>0.04</TD></TR><TR><TD>201211</TD><TD>0.01</TD><TD>0.02</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.03</TD><TD>0.00</TD></TR><TR><TD>201212</TD><TD>0.02</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.06</TD><TD>0.00</TD><TD>0.02</TD><TD>0.03</TD></TR><TR><TD>201301</TD><TD>0.02</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.02</TD></TR><TR><TD>201302</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.05</TD></TR><TR><TD>201303</TD><TD>0.01</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD></TR><TR><TD>201304</TD><TD>0.00</TD><TD>0.04</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.03</TD><TD>0.02</TD><TD>0.04</TD><TD>-0.01</TD></TR><TR><TD>201305</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD></TR><TR><TD>201306</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.02</TD><TD>0.00</TD><TD>0.02</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD></TR><TR><TD>201307</TD><TD>0.01</TD><TD>0.01</TD><TD>0.04</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.02</TD></TR><TR><TD>201308</TD><TD>0.02</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.01</TD><TD>0.03</TD><TD>0.12</TD><TD>0.00</TD><TD>0.01</TD></TR><TR><TD>201309</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.02</TD><TD>0.03</TD><TD>0.03</TD><TD>0.00</TD><TD>-0.01</TD><TD>0.03</TD></TR><TR><TD>201310</TD><TD>0.01</TD><TD>0.00</TD><TD>0.00</TD><TD>0.01</TD><TD>0.00</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.00</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>avg</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.01</TD><TD>0.02</TD></TR></TBODY></TABLE> Thu, 29 Oct 2020 12:40:57 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695151#M212080 Cho8 2020-10-29T12:40:57Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695153#M212082 <BLOCKQUOTE><HR /><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/354671">@Cho8</a>&nbsp;wrote:<BR /> <P>its vertical summation..not by column</P> <HR /></BLOCKQUOTE> <P>It sure seems you want to do this by column, but you say "NOT by column". Can you please explain?</P> Thu, 29 Oct 2020 12:45:41 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695153#M212082 PaigeMiller 2020-10-29T12:45:41Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695154#M212083 <P>please check my post on how result should look like..</P> Thu, 29 Oct 2020 12:48:30 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695154#M212083 Cho8 2020-10-29T12:48:30Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695157#M212086 <P>Use PROC REPORT:</P> <PRE><CODE class=" language-sas">data have; input y $ d1-d9; datalines; 201201 0.01 0.00 0.01 0.00 0.00 0.00 0.01 0.01 0.01 201202 0.03 -0.01 0.02 0.01 0.00 0.00 0.01 0.00 0.04 201203 0.01 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.02 201204 0.02 0.01 0.00 0.01 0.02 0.00 0.00 0.00 0.01 201205 0.01 0.00 0.00 0.02 0.00 0.00 0.01 0.00 0.03 201206 0.00 0.01 0.01 0.02 0.00 0.00 0.00 0.00 0.01 201207 0.01 0.02 0.00 0.00 0.00 0.00 0.03 0.00 0.02 201208 0.01 0.00 0.00 0.01 0.01 0.00 0.02 0.02 0.00 201209 0.01 0.01 0.00 0.03 0.00 0.02 0.01 0.00 0.02 201210 0.02 0.00 0.00 0.01 0.04 0.03 0.02 0.01 0.04 201211 0.01 0.02 0.01 0.00 0.00 0.00 0.00 0.03 0.00 201212 0.02 0.00 0.01 0.00 0.01 0.06 0.00 0.02 0.03 201301 0.02 0.02 0.00 0.00 0.00 0.00 0.00 0.00 0.02 201302 0.00 0.00 0.01 0.00 0.00 0.01 0.00 0.01 0.05 201303 0.01 0.01 0.00 0.01 0.00 0.01 0.00 0.01 0.00 201304 0.00 0.04 0.01 0.01 0.01 0.03 0.02 0.04 -0.01 ; proc report data=have; column y d:; define y / display; define d: / analysis mean format=7.2; rbreak after / summarize; run;</CODE></PRE> Thu, 29 Oct 2020 12:50:42 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695157#M212086 Kurt_Bremser 2020-10-29T12:50:42Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695158#M212087 <P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/11562">@Kurt_Bremser</a>, I think this a bit too simple since the window of interest changes for each d1, d2 ...&nbsp;</P> Thu, 29 Oct 2020 12:53:55 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695158#M212087 PeterClemmensen 2020-10-29T12:53:55Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695159#M212088 <P>But it creates the expected output as displayed. I first suspected a need for a rolling window, but the OP seems to want one summary line for all numbers.</P> Thu, 29 Oct 2020 12:57:31 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695159#M212088 Kurt_Bremser 2020-10-29T12:57:31Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695160#M212089 <P>for d1..average from 201202 to 201302..</P><P>&nbsp;</P><P>for d2 average from 201203 to 201303..</P><P>&nbsp;</P><P>for d3 average from 201204 to 201304..</P><P>&nbsp;</P><P>&nbsp;</P><P>and so on..</P> Thu, 29 Oct 2020 13:00:29 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695160#M212089 Cho8 2020-10-29T13:00:29Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695162#M212090 <P>Is this for reporting purposes or do you want a SAS data set like this?</P> Thu, 29 Oct 2020 13:05:29 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695162#M212090 PeterClemmensen 2020-10-29T13:05:29Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695172#M212096 <BLOCKQUOTE><HR /><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/31304">@PeterClemmensen</a>&nbsp;wrote:<BR /><P>Is this for reporting purposes or do you want a SAS data set like this?</P><HR /></BLOCKQUOTE><P>Need dataset like that..</P> Thu, 29 Oct 2020 13:34:22 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695172#M212096 Cho8 2020-10-29T13:34:22Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695179#M212099 <P>Do you have SAS/IML ? I would like to use IML code for this kind of question.</P> <P>&nbsp;</P> <PRE>data have; input y $ d1-d9; datalines; 201201 0.01 0.00 0.01 0.00 0.00 0.00 0.01 0.01 0.01 201202 0.03 -0.01 0.02 0.01 0.00 0.00 0.01 0.00 0.04 201203 0.01 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.02 201204 0.02 0.01 0.00 0.01 0.02 0.00 0.00 0.00 0.01 201205 0.01 0.00 0.00 0.02 0.00 0.00 0.01 0.00 0.03 201206 0.00 0.01 0.01 0.02 0.00 0.00 0.00 0.00 0.01 201207 0.01 0.02 0.00 0.00 0.00 0.00 0.03 0.00 0.02 201208 0.01 0.00 0.00 0.01 0.01 0.00 0.02 0.02 0.00 201209 0.01 0.01 0.00 0.03 0.00 0.02 0.01 0.00 0.02 201210 0.02 0.00 0.00 0.01 0.04 0.03 0.02 0.01 0.04 201211 0.01 0.02 0.01 0.00 0.00 0.00 0.00 0.03 0.00 201212 0.02 0.00 0.01 0.00 0.01 0.06 0.00 0.02 0.03 201301 0.02 0.02 0.00 0.00 0.00 0.00 0.00 0.00 0.02 201302 0.00 0.00 0.01 0.00 0.00 0.01 0.00 0.01 0.05 201303 0.01 0.01 0.00 0.01 0.00 0.01 0.00 0.01 0.00 201304 0.00 0.04 0.01 0.01 0.01 0.03 0.02 0.04 -0.01 ; proc transpose data=have out=temp name=y prefix=_; var d1-d9; id y; run; data temp; set temp; array x{*} _:; count=0; do i=1 to 13; n=_n_+i; if n&gt;dim(x) then leave; count+1; sum=sum(sum,x{n}); end; _avg=sum/count; drop i n count sum; run; proc transpose data=temp out=want; var _:; id y; run; </PRE> Thu, 29 Oct 2020 13:52:21 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695179#M212099 Ksharp 2020-10-29T13:52:21Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695181#M212101 <BLOCKQUOTE><HR /><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/18408">@Ksharp</a>&nbsp;wrote:<BR /><P>Do you have SAS/IML ? I would like to use IML code for this kind of question.</P><P>&nbsp;</P><PRE>data have; input y $ d1-d9; datalines; 201201 0.01 0.00 0.01 0.00 0.00 0.00 0.01 0.01 0.01 201202 0.03 -0.01 0.02 0.01 0.00 0.00 0.01 0.00 0.04 201203 0.01 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.02 201204 0.02 0.01 0.00 0.01 0.02 0.00 0.00 0.00 0.01 201205 0.01 0.00 0.00 0.02 0.00 0.00 0.01 0.00 0.03 201206 0.00 0.01 0.01 0.02 0.00 0.00 0.00 0.00 0.01 201207 0.01 0.02 0.00 0.00 0.00 0.00 0.03 0.00 0.02 201208 0.01 0.00 0.00 0.01 0.01 0.00 0.02 0.02 0.00 201209 0.01 0.01 0.00 0.03 0.00 0.02 0.01 0.00 0.02 201210 0.02 0.00 0.00 0.01 0.04 0.03 0.02 0.01 0.04 201211 0.01 0.02 0.01 0.00 0.00 0.00 0.00 0.03 0.00 201212 0.02 0.00 0.01 0.00 0.01 0.06 0.00 0.02 0.03 201301 0.02 0.02 0.00 0.00 0.00 0.00 0.00 0.00 0.02 201302 0.00 0.00 0.01 0.00 0.00 0.01 0.00 0.01 0.05 201303 0.01 0.01 0.00 0.01 0.00 0.01 0.00 0.01 0.00 201304 0.00 0.04 0.01 0.01 0.01 0.03 0.02 0.04 -0.01 ; proc transpose data=have out=temp name=y prefix=_; var d1-d9; id y; run; data temp; set temp; array x{*} _:; count=0; do i=1 to 13; n=_n_+i; if n&gt;dim(x) then leave; count+1; sum=sum(sum,x{n}); end; _avg=sum/count; drop i n count sum; run; proc transpose data=temp out=want; var _:; id y; run; </PRE><HR /></BLOCKQUOTE><P>i dnt have SAS IML.. Need solution in Datastep or Macro</P> Thu, 29 Oct 2020 13:58:49 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695181#M212101 Cho8 2020-10-29T13:58:49Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695197#M212104 <P>Here is IML code , if you like it.</P> <P>&nbsp;</P> <PRE>data have; input y $ d1-d9; datalines; 201201 0.01 0.00 0.01 0.00 0.00 0.00 0.01 0.01 0.01 201202 0.03 -0.01 0.02 0.01 0.00 0.00 0.01 0.00 0.04 201203 0.01 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.02 201204 0.02 0.01 0.00 0.01 0.02 0.00 0.00 0.00 0.01 201205 0.01 0.00 0.00 0.02 0.00 0.00 0.01 0.00 0.03 201206 0.00 0.01 0.01 0.02 0.00 0.00 0.00 0.00 0.01 201207 0.01 0.02 0.00 0.00 0.00 0.00 0.03 0.00 0.02 201208 0.01 0.00 0.00 0.01 0.01 0.00 0.02 0.02 0.00 201209 0.01 0.01 0.00 0.03 0.00 0.02 0.01 0.00 0.02 201210 0.02 0.00 0.00 0.01 0.04 0.03 0.02 0.01 0.04 201211 0.01 0.02 0.01 0.00 0.00 0.00 0.00 0.03 0.00 201212 0.02 0.00 0.01 0.00 0.01 0.06 0.00 0.02 0.03 201301 0.02 0.02 0.00 0.00 0.00 0.00 0.00 0.00 0.02 201302 0.00 0.00 0.01 0.00 0.00 0.01 0.00 0.01 0.05 201303 0.01 0.01 0.00 0.01 0.00 0.01 0.00 0.01 0.00 201304 0.00 0.04 0.01 0.01 0.01 0.03 0.02 0.04 -0.01 ; proc iml; use have(keep=d:) nobs nobs; read all var _num_ into x[c=vname] ; close; avg=j(1,ncol(x),.); do i=1 to ncol(x); l=i+1; u=(i+13)&gt;&lt;nobs; avg[i]=x[l:u,i][:]; end; create avg from avg[c=vname]; append from avg; close; quit; data want; set have avg ; run; </PRE> Thu, 29 Oct 2020 14:13:01 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695197#M212104 Ksharp 2020-10-29T14:13:01Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695202#M212107 <BLOCKQUOTE><HR /><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/354671">@Cho8</a>&nbsp;wrote:<BR /><BLOCKQUOTE><HR /><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/18408">@Ksharp</a>&nbsp;wrote:<BR /><P>Do you have SAS/IML ? I would like to use IML code for this kind of question.</P><P>&nbsp;</P><PRE>data have; input y $ d1-d9; datalines; 201201 0.01 0.00 0.01 0.00 0.00 0.00 0.01 0.01 0.01 201202 0.03 -0.01 0.02 0.01 0.00 0.00 0.01 0.00 0.04 201203 0.01 0.00 0.01 0.00 0.00 0.00 0.00 0.00 0.02 201204 0.02 0.01 0.00 0.01 0.02 0.00 0.00 0.00 0.01 201205 0.01 0.00 0.00 0.02 0.00 0.00 0.01 0.00 0.03 201206 0.00 0.01 0.01 0.02 0.00 0.00 0.00 0.00 0.01 201207 0.01 0.02 0.00 0.00 0.00 0.00 0.03 0.00 0.02 201208 0.01 0.00 0.00 0.01 0.01 0.00 0.02 0.02 0.00 201209 0.01 0.01 0.00 0.03 0.00 0.02 0.01 0.00 0.02 201210 0.02 0.00 0.00 0.01 0.04 0.03 0.02 0.01 0.04 201211 0.01 0.02 0.01 0.00 0.00 0.00 0.00 0.03 0.00 201212 0.02 0.00 0.01 0.00 0.01 0.06 0.00 0.02 0.03 201301 0.02 0.02 0.00 0.00 0.00 0.00 0.00 0.00 0.02 201302 0.00 0.00 0.01 0.00 0.00 0.01 0.00 0.01 0.05 201303 0.01 0.01 0.00 0.01 0.00 0.01 0.00 0.01 0.00 201304 0.00 0.04 0.01 0.01 0.01 0.03 0.02 0.04 -0.01 ; proc transpose data=have out=temp name=y prefix=_; var d1-d9; id y; run; data temp; set temp; array x{*} _:; count=0; do i=1 to 13; n=_n_+i; if n&gt;dim(x) then leave; count+1; sum=sum(sum,x{n}); end; _avg=sum/count; drop i n count sum; run; proc transpose data=temp out=want; var _:; id y; run; </PRE><HR /></BLOCKQUOTE><P>i dnt have SAS IML.. Need solution in Datastep or Macro</P><HR /></BLOCKQUOTE><P>i didnot get the logic of array part..can you just explain it..</P> Thu, 29 Oct 2020 14:25:27 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695202#M212107 Cho8 2020-10-29T14:25:27Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695437#M212223 array x{*} _:; /*Refer to variables _201201 _201202 _201203 ...........*/<BR /> count=0; /* how many obs should be used for MEAN*/<BR /> do i=1 to 13; /*Rolling windows */<BR /> n=_n_+i; /* when d1(first obs) start with 1+1=2 ,then 1+2=3 1+3=4 ......<BR /> when d2(second obs) start with 2+1=3 ,then 2+2=4 2+3=5 ....*/<BR /><BR /> if n&gt;dim(x) then leave; /*If the length of windows is longer than the number of obs,<BR /> then don't run the following code, and enter the next loop*/<BR /><BR /> count+1; /*how many obs have been used for calculated AVG*/<BR /> sum=sum(sum,x{n}); /*calculated SUM of 201202 - 201302 for d1 */<BR /> end;<BR /> _avg=sum/count; /*calculated avg/mean */ Fri, 30 Oct 2020 11:53:19 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/695437#M212223 Ksharp 2020-10-30T11:53:19Z Re: Vertical summation by condition https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/697613#M213221 <P>If missing value, i want to next value.. i.e count start from non-missing value</P> Mon, 09 Nov 2020 13:49:49 GMT https://communities.sas.com/t5/SAS-Programming/Vertical-summation-by-condition/m-p/697613#M213221 Cho8 2020-11-09T13:49:49Z