turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Average of last x entries in each column of a tria...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-05-2015 05:35 AM

Period | 1 | 2 | 3 | 4 | 5 | 6 |
---|---|---|---|---|---|---|

... | ... | ... | ... | ... | ... | ... |

201401 | 22223 | 30303 | 40000 | 80000 | 123444 | 144000 |

201402 | 22334 | 40002 | 100202 | 120000 | 132000 | . |

201403 | 129934 | 150000 | 123444 | 130000 | . | . |

201404 | 54349 | 75000 | 123444 | . | . | . |

201501 | 99900 | 134423 | . | . | . | . |

201502 | 102030 | . | . | . | . | . |

I have created triangles of data in SAS. When I say triangles, the cells off the triangle are left blank. The actual triangle is much bigger than this one and I'm trying to write the code to sum over the bottom nonblank cells of each column. I'm not sure if this is possible but I would think it would involve proc summary.

Are there any functions that can achieve this?

Thank you

Accepted Solutions

Solution

08-05-2015
06:39 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

08-05-2015 06:39 AM

Hi,

You could use arrays:

data want;

set have;

array var{6}; /* I assume your variables are prefixed with var */

do i=1 to 6;

if var{i}=. then do;

do j=i-1 to 1 by -1;

result=sum(result,var{j});

end;

leave;

end;

end;

run;

So the above looks for the last record (i.e. the first .), then takes the previous numbers and sums them up. You could do mean of certain, e.g. change the to 1 to 3, and mean is just sum / i-1.

All Replies

Solution

08-05-2015
06:39 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

08-05-2015 06:39 AM

Hi,

You could use arrays:

data want;

set have;

array var{6}; /* I assume your variables are prefixed with var */

do i=1 to 6;

if var{i}=. then do;

do j=i-1 to 1 by -1;

result=sum(result,var{j});

end;

leave;

end;

end;

run;

So the above looks for the last record (i.e. the first .), then takes the previous numbers and sums them up. You could do mean of certain, e.g. change the to 1 to 3, and mean is just sum / i-1.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-05-2015 07:31 AM

Thanks RW9

That works when summing the last 6 cells on a row but I wanted to get the average of the last 8 nonzero cells on each column. The length of the triangle is 36*36 so I want to take the average of the last 8.

Using the above example (and assuming I only want the average of the last 2 non-zero cells I would want

(102030+99900)/2 = 100965

(134423+75000)/2 = 104711.5

(123444+123444)/2 = 123444

(130000+120000)/2 = 125000

(132000+123444)/2 = 127722

144000 (where there are less than the number we want to get to average over it just gets the average of what's there|).

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-05-2015 07:33 AM

Seems like a job for STAT functions more that array loops to me.

infile cards4 dsd missover;

input period:yymmn. var1-var6;

format period yymmn.;

cards;

201401,22223,30303,40000,80000,123444,144000

201402,22334,40002,100202,120000,132000,.

201403,129934,150000,123444,130000,.,.

201404,54349,75000,123444,.,.,.

201501,99900,134423,.,.,.,.

201502,102030,.,.,.,.,.

;;;;

set tri;

array v

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to data_null__

08-05-2015 09:41 AM

I ended up using this but it's a bit messy...

data average;

input key $4.;

datalines;

mean

run;

%macro average;

%do j = 0 %to 36;

data test&j;

set test;

if qtr&j = . then delete;

key = "mean";

keep key qtr&j;

run;

data test&j;

set test&j(obs=10);

run;

proc summary data = test&j nway missing;

class key;

var qtr&j;

output out = test&j(drop = _freq_ _type_) mean=;

run;

data average;

merge average test&j;

by key;

run;

%end;

%mend;

%average;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to brophymj

08-05-2015 07:14 AM

I don't understand what you want. For the data you show what values are summed? You imply there is more than one upper triangular matrix, if so show example and if there is variable to differentiate.

The way I read your explanation you want

x = sum(

put x=;

x=