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
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.
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
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))
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.
Thank you very much!! Your answer is correct and really helpful 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.