How do I code to sum every 7 rows for a variable and code for output per week

[carmong]

This is a two part question.

Part 1:

Is there a way to write code that will sum every 7 rows for a variable. I wrote code for this in excel  (=SUM(OFFSET($D$2,(ROW()-ROW($D$2))*7,0,7,1))  but I will now have to do this for a much larger dataset. Is there a way to write code in sas that will do the same?

Part 2:

The code posted below outputs data in a "by month" format. How can this be done to output data in a "per week" format (meaning the data is displayed with the last date of a 7 day period)

/* monthly analysis */
proc freq data= admit_adults;
table def*admissiondate / out=freqmonth&today. outpct;
format admissiondate MONYY.;
run;

[Tom]

From the PICTURE it looks like you have both a WEEK variable and a DATE variable.

That seems critical since your picture is also showing 5 observations per week and not the 7 you mentioned in the question. 

You can just use the WEEK variable.  If for some reason your actual data is different than the picture you posted and there is no WEEK variable then you could use a FORMAT to allow SAS to summarize by WEEK directly from the DATE values.

proc summary data=admit_adults nway;
  by week ;
  var sales ;
  output out=WANT sum=SALES ;
run;

If you want to summarize by WEEK you could any of the multiple WEEK formats (WEEKV, WEEKU, WEEKW).

If you actually want to move the dates to the last day of the week use the INTNX() function to create a new date variable with the value of the last day of the week.

data end_of_week;
  set have;
  end_of_week=intnx('week',date,0,'e');
  format end_of_week yymmdd10.;
run;

[carmong]

The picture was used as an example of how my data is set up, but that is not my data. My data has dates for seven days of the week along with number of admissions per day. 

[ballardw]

One way if that "date" is actually a SAS date value:

proc freq data= admit_adults;
   table def*admissiondate / out=freqweek&today. outpct;
   format admissiondate WeekU5.;
run;

There are 3 SAS supplied formats that will group data to week intervals when using a width of 5 or 6. The details come into how to treat dates for weeks that cross year boundaries and when to treat a date as the start of the week. Look up the details for WeekUw, WeekVw and WeekWw.

If you don't like the yyWww (2-digit year, a capital W, 2-digit week number you could create a custom format. Or assign a different format for the values when used in other procedures.

Note that if your use the WeekU3. format that displays values like W05 (fifth week) that is going to combine values across years if your data has enough records.

Note that any format based approach has the limits of the format. If you do not have dates of  01Jan, 02 Jan, etc, then your "week" is quite likely not going to correspond to one of these formats. At which point you really need to provide example data and what your rule is for defining "week".

Warning: Pictures of data that is not identical to what you actually have only adds confusion and may result in code that does not work as desired because we cannot tell any property of variables in pictures.

[carmong]

Posted below is an example of two weeks of data

Today's Date	Hospital admissions
5/31/2020	383
6/1/2020	369
6/2/2020	430
6/3/2020	441
6/4/2020	437
6/5/2020	411
6/6/2020	372
6/7/2020	373
6/8/2020	424
6/9/2020	426
6/10/2020	397
6/11/2020	413
6/12/2020	429
6/13/2020	381

[Tom · Accepted solution]

First let's convert your listing into an actual dataset.  Let's not use a potentially confusing format to display the dates. (Is the second value June first or the sixth of January?)

data have;
  input date :mmddyy. admissions ;
  format date yymmdd10.;
cards;
5/31/2020 383
6/1/2020 369
6/2/2020 430
6/3/2020 441
6/4/2020 437
6/5/2020 411
6/6/2020 372
6/7/2020 373
6/8/2020 424
6/9/2020 426
6/10/2020 397
6/11/2020 413
6/12/2020 429
6/13/2020 381
;

Now let's generate a week variable.  You said you wanted to use the end of the week.

data want;
  set have;
  week_end = intnx('week',date,0,'e');
  format week_end yymmdd10.;
run;

Which you can then use to summarize.

proc summary data=want nway ;
  class week_end;
  var admissions;
  output out=summary sum=weekly_admissions;
run;

Result

                                           weekly_
Obs      week_end    _TYPE_    _FREQ_    admissions

 1     2020-06-06       1         7         2843
 2     2020-06-13       1         7         2843

[carmong]

the code works but my output is truncated. I am using an excel sheet that has 1,020 rows with dates from March 29th 2020 going to January 13th 2023 but the summary data is only outputting up until 3/12/2022. What might be causing that to happen?  

[PaigeMiller]

What might cause that to happen? Any one of a number of things:

• The import from Excel didn't work right, and so that is the problem
• a mixture of date formats in Excel causes the import in SAS to get confused.
• Your data may not be as you have described it to us
• There could be errors in the data (it imported properly from Excel, but it wasn't correct)
• Other things?

You need to look carefully at the SAS data set (not the Excel file) that is created, and see if everything looks kosher in there.

[Kurt_Bremser]

The Excel sheet is irrelevant. What's in the SAS dataset?

[carmong]

[PaigeMiller]

You need to look at the data in the SAS data set and see if there are errors or things that aren't what you expect.

[Tom]

Why are some of the variables where the label starts with NUMBER defined as character?

Does the XLSX file accidentally have text strings instead of numbers in some of those columns?

[carmong]

The spreadsheet is uploaded by another department so they may have changed the format. What I will have to do is save it to my own folder instead of importing directly from their folder

[carmong]

For some reason the data was truncated when changing the admissions from character to numeric (can't explain why), but when I changed the character to numeric before running the rest of the code, the sas data was not affected in any way and the code gave me all of my data 🙂

[Tom]

---

@carmong wrote:

The spreadsheet is uploaded by another department so they may have changed the format. What I will have to do is save it to my own folder instead of importing directly from their folder

---

XLSX files are not a very reliable data entry tool (without adding a lot of coding under the hood somewhere).  That is not what spreadsheets are defined to do.

To more consistently convert the files to SAS datasets I would recommend writing them to CSV files and then reading those into SAS.  Not only could you then properly define the variables as numeric you could use shorter (easier to type and read) variable names instead of trying the use the report column headers as the variable names.