BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Maria_Sissel
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
mfab
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Astounding
PROC Star

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.

Maria_Sissel
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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