how to SUM time (1:30, 1:45 etc.) by user ID

[msrenee1984]

Hello, I'm inquiring how to SUM a column of student hours (1:30, 1:45, etc.) by their student ID, class subject, and YEAR.  Would like my report to show total class hours by each student for each year for year subject("sType").  I've attached an example...actual dataset has thousands of rows.  Thank you for any assistance!

[PaigeMiller]

Any method in SAS that sums a numeric variable will work. 

Almost no one here will download Excel files as they can be security threats. The best way to include data in your example is via working SAS data step  code (instructions and examples).

For example, here is code using PROC SUMMARY which does the summing. I have made up variable names since I don't know the variable names in your Excel file. This assumes that 1:30 (one hour 30 minutes) is numeric and unformatted appears as 5400 (which is 60 seconds per minute * 60 minutes per hour * 1.5 hours). It gives the sum of the number of seconds. It also works if 1 hour and 30 minutes is represented as 1.5, then it gives the sum of the number of hours.

proc summary data = ____________;
    class stype year student_id;
    var hours;
    output out=sums sum=sum_hours;
run;

[msrenee1984]

Thank you very much Paige... I will reframe from uploading excel and just post my code in my question!!! I will try the PROC summary now!! (Yes, its 1hr and 30 minutes!)

[msrenee1984]

I'm getting an error message that says "ERROR: Variable HOURS in list does not match type prescribed for this list.  I'm unsure how to convert the "hours" variable...

[PaigeMiller]

Your hours must be character variables, not numeric variables. You can't sum character variables. You can convert these into actual numbers. If you can show us how the variable hours appears in your data (just type in an example), we can advise further.

[msrenee1984]

Yes, I just double checked it's a character variable CHAR length 5.  Attaching the actual column below.  Thank you!

HOURS

1:30

1:45

1:30

1:45

0:00

1:30

1:45

1:30

1:45

1:30

1:45

1:30

1:45

0:00

1:30

1:45

0:00

1:00

1:30

1:45

2:00

1:30

1:45

1:30

1:45

1:30

1:45

1:30

1:45

0:00

[PaigeMiller · Accepted solution]

data new;
    set old;
    hours_n=input(hours,hhmmss5.);
    format hours_n hhmm5.;
run;

Data set NEW should be run through PROC SUMMARY using the new variable named HOURS_N, and PROC SUMMARY should do the summing properly.

[msrenee1984]

this worked perfectly, thank you very much!!!

[Kurt_Bremser]

---

@msrenee1984 wrote:

I'm getting an error message that says "ERROR: Variable HOURS in list does not match type prescribed for this list.  I'm unsure how to convert the "hours" variable...

---

This means that your hours variable is most probably of type character.

Follow the instructions posted by @PaigeMiller and post the resulting code. This is mandatory!

Only then will we know important variable attributes (type, length, format) and the real content we have to work with.

Excel spreadsheets do not have this information, only what Excel thinks about it.

Also answer questions like where does the data originate from, and how do you read it into your SAS environment?

[Kurt_Bremser]

Also note that the communities website does not provide the usual online preview which would allow us to view the spreadsheet without downloading. This is suspicious.