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
- /
- How to sum horizontally from af certain point (spe...

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

04-09-2015 04:47 AM

We have a question about how to sum horizontally in SAS.

Our dataset contains several horizontal rows, each one representing a person.

Each column represents one week and all the week-variables are numeric (the time period is from year 1999-2001, thus the total number of columns/weeks is 52*52*52=156)

All observations contain one of the following values: “1”, “0” or “.” (“.” = missing values).

We would really appreciate if anyone would like to help us with a SAS code enabling us to sum one row horizontally (i.e. a time period for each person of the dataset) and thereby generate a variable, which shows the sum from the first time you meet the value “1” in a row and from that point sums 52 weeks ahead (1 year).

The “1” observations do not necessarily have to be coherent in the row, i.e. even though the sum-variable meets the value “0” or “.” it shall continue summing from the starting point (first time meeting the “1”) and 52 weeks ahead.

The first observed “1” has to be placed in a week in the period 1999-2001, but the following observations are also allowed to be places outside the period (e.g. in year 2002), as long as the first observation is placed in a week between 1999-2001.

Thank you in advance.

Best regards,

Maria and Sissel

Accepted Solutions

Solution

04-09-2015
11:02 AM

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

Posted in reply to Maria_Sissel

04-09-2015 11:02 AM

Well, I've got to assume that this data exists in SAS, and you know the names of the variables. You could code it this way:

data want;

set have;

array weeks {*} list of all week variables in order starting with Jan1999;

total = 0;

do _i_=1 to 156 until (first_1 > .);

if weeks{_i_}=1 then first_1 = _i_;

end;

if (. < first_1 <= 156) then do _i_ = first_1 to min(first_1 + 52, dim(weeks));

total + weeks{_i_};

end;

run;

The first loop locates the first "1". The second loop sums from that point. It's up to you whether you use first_1 + 52 (a total of 53 weeks) or first_1 + 51 (a total of 52 weeks). The code is obviously untested, but should be easy enough to work with. Good luck.

All Replies

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

Posted in reply to Maria_Sissel

04-09-2015 09:52 AM

Hi Maria, hi Sissel,

from what I have understood, your data looks like this:

Person | 1/1999 | 2/1999 | 3/1999 |
---|---|---|---|

abc | . | . | 0 |

def | 1 | . | 0 |

acb | . | 1 | 0 |

bca | 1 | 1 | 1 |

cba | . | . | 1 |

While there may surely be several approaches to solve this, I would try to transpose the data and make it look like this:

Person | Week | value |
---|---|---|

abc | 1/1999 | . |

abc | 2/1999 | . |

abc | 3/1999 | 0 |

def | 1/1999 | 1 |

def | 2/1999 | . |

def | 3/1999 | 0 |

acb | 1/1999 | . |

From then on you could just use a DataStep with retain statement to calculate the values and then output the specific results.

Does this help?

Cheers, Michael

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

Posted in reply to Maria_Sissel

04-09-2015 09:57 AM

Hi,

Good idea to provide some test data/ required output. You should be able to do this using the SUM() function in SQL, by using where clauses and sub-queries with min(). Something like:

select COUNT(1)

from BASE_DATASET A

where DATE > (select DATE from BASE_DATESET where FLAG=1 and MIN(DATE))

Solution

04-09-2015
11:02 AM

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

Posted in reply to Maria_Sissel

04-09-2015 11:02 AM

Well, I've got to assume that this data exists in SAS, and you know the names of the variables. You could code it this way:

data want;

set have;

array weeks {*} list of all week variables in order starting with Jan1999;

total = 0;

do _i_=1 to 156 until (first_1 > .);

if weeks{_i_}=1 then first_1 = _i_;

end;

if (. < first_1 <= 156) then do _i_ = first_1 to min(first_1 + 52, dim(weeks));

total + weeks{_i_};

end;

run;

The first loop locates the first "1". The second loop sums from that point. It's up to you whether you use first_1 + 52 (a total of 53 weeks) or first_1 + 51 (a total of 52 weeks). The code is obviously untested, but should be easy enough to work with. Good luck.

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

Posted in reply to Astounding

04-18-2015 06:43 AM

Thank you very much!! Your answer is correct and really helpful :-)