topic Re: How to sum horizontally from af certain point (specific value appears) in the row and 52 weeks ahead in SAS Procedures

How to sum horizontally from af certain point (specific value appears) in the row and 52 weeks ahead

Maria_Sissel — Thu, 09 Apr 2015 08:47:48 GMT

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 525252=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

Re: How to sum horizontally from af certain point (specific value appears) in the row and 52 weeks ahead

mfab — Thu, 09 Apr 2015 13:52:11 GMT

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

Re: How to sum horizontally from af certain point (specific value appears) in the row and 52 weeks ahead

RW9 — Thu, 09 Apr 2015 13:57:02 GMT

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))

Re: How to sum horizontally from af certain point (specific value appears) in the row and 52 weeks ahead

Astounding — Thu, 09 Apr 2015 15:02:22 GMT

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.

Re: How to sum horizontally from af certain point (specific value appears) in the row and 52 weeks ahead

Maria_Sissel — Sat, 18 Apr 2015 10:43:52 GMT

Thank you very much!! Your answer is correct and really helpful 🙂