Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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


Accepted Solutions
Solution
‎04-09-2015 11:02 AM
Super User
Posts: 5,503

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

Posted in reply to Maria_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.

View solution in original post


All Replies
Frequent Contributor
Posts: 126

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

Posted in reply to Maria_Sissel

Hi Maria, hi Sissel,

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

Person1/19992/19993/1999
abc..0
def1.0
acb.10
bca111
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:

PersonWeekvalue
abc1/1999.
abc2/1999.
abc3/19990
def1/19991
def2/1999.
def3/19990
acb1/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

Super User
Super User
Posts: 7,950

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

Posted in reply to Maria_Sissel

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
Super User
Posts: 5,503

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

Posted in reply to Maria_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.

New Contributor
Posts: 3

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

Posted in reply to Astounding

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 946 views
  • 1 like
  • 4 in conversation