topic Re: How to Sum Vertically in SAS Programming

How to Sum Vertically

alterman1 — Wed, 18 Apr 2018 00:58:47 GMT

If I have a field that's a datetime value, how do I correctly sum(time-part) using proc sql? I don't have a problem getting the 'timepart' out of the timestamp, but if I try to 'group by' with a 'sum' on that 'time value', I get the same number in every record for time.

Proc SQL doesn't seem to handle date/time arithmetic the same way SQL does on a DBMS. If this can't be done using Proc SQL, can someone recommend the next best method? I need to hand off this program to a DBA, so I need something nice and clean that a DBA will understand.

Re: How to Sum Vertically

ShenQicheng — Wed, 18 Apr 2018 01:09:15 GMT

Can you provide a code sample and the results that can describe you problem?

Re: How to Sum Vertically

Reeza — Wed, 18 Apr 2018 01:28:20 GMT

Depending on how you selected the data you would get either the GROUP variables + the summary statistics or you'll get all records plus the summary statistic repeated.

Since you don't show your code we can't say what you did wrong. See the example below, and run them to the see the different forms of output.

proc sql;
create table demo1 as
select *, mean(height) as avg_height
from sashelp.class
group by sex;
create table demo2 as
select sex, mean(height) as avg_height
from sashelp.class
group by sex;
quit;

proc print data=demo1;
proc print data=demo2;
run;

I've made some assumptions about your question because you didn't show any code. If this doesn't answer your question, please include your code and log illustrating the issue. If you use the SASHELP data sets we can replicate your work as well on our machines to test a solution.

@alterman1 wrote:

If I have a field that's a datetime value, how do I correctly sum(time-part) using proc sql? I don't have a problem getting the 'timepart' out of the timestamp, but if I try to 'group by' with a 'sum' on that 'time value', I get the same number in every record for time.

Proc SQL doesn't seem to handle date/time arithmetic the same way SQL does on a DBMS. If this can't be done using Proc SQL, can someone recommend the next best method? I need to hand off this program to a DBA, so I need something nice and clean that a DBA will understand.

Re: How to Sum Vertically

alterman1 — Wed, 18 Apr 2018 01:38:49 GMT

Re: How to Sum Vertically

alterman1 — Wed, 18 Apr 2018 01:40:39 GMT

The above attachment shows the data before summarization, after summerization, and the PROC SQL code I'm trying to summerize it with.

Re: How to Sum Vertically

alterman1 — Wed, 18 Apr 2018 01:48:10 GMT

Hi Reeza. I've added my PRO SQL code and samples of my output before and after.

Re: How to Sum Vertically

Reeza — Wed, 18 Apr 2018 02:08:12 GMT

I think your error is logical not code, but I can't really tell because the variable names don't align with the data you've shown and scrolling between pages doesn't make it any easier to figure it out which variables are which.

In a typical SQL query you would need to have all the GROUP BY variables included on your SELECT statement and all other variables must be summary. SAS doesn't require this, but if you don't do it, it returns every line.

Please include your images and code directly into the forum and as text, not as screenshots, it makes it infinitely harder to read.

@alterman1 wrote:

Hi Reeza. I've added my PRO SQL code and samples of my output before and after.

Re: How to Sum Vertically

ShenQicheng — Wed, 18 Apr 2018 02:14:04 GMT

Try
Group by QWHSSSID, SYSTEM_DATE
instead of
Group by QWHSSSID, DATEPART(SMFTIME)

Re: How to Sum Vertically

alterman1 — Wed, 18 Apr 2018 02:25:38 GMT

Reeza, I displayed the code the only way I can. I don't have any what of downloading the code into a text file or transferring the file with email or ftp;

If you look at my proc sql, there are 3 fields - SMFTIME which is the timestamp that I am extracting the date from and trying to group by. QWHSSSID is the other variable that appears in the group by clause. They are the two fields I'm trying to summarize by.
The third and last field is QPACTJST which is a time12.2 field. This is the field that I want to sum using QWHSSSID and DATEPART(SMFTIME) to group my data. If there is a logic error, I don't see it.

If I tried to group by a string QWHSSSID and a DATE in DB2, this would work perfectly with just using a DATE column function against SMFTIME (which, again, is a timestamp column). It looks like PROC SQL handles this differently and I've looked through a ton of content trying to find an example of someone summing a time field by some key.

Re: How to Sum Vertically

alterman1 — Wed, 18 Apr 2018 02:33:23 GMT

I grouped by QWHSSID and SYSTEMS_DATE and I still get the same result. CPU_TIME the same value in every row.

Re: How to Sum Vertically

ShenQicheng — Wed, 18 Apr 2018 02:39:50 GMT

As Reeza said, please post your code directly into the forum in text format.

Re: How to Sum Vertically

alterman1 — Wed, 18 Apr 2018 02:49:32 GMT

I can't transfer it, so I typed it in. Hopefully no typing errors.

OPTIONS PAGESIZE=40 LINESIZE=250 PAGENO=1 NOCENTER;

LIBNAME DETAIL;

PROC DATASETS LIBRARY=DETAIL;

DATA DB2ACCTP;

SET DETAIL.DB2ACCTP;

PROC CONTENTS;

PROC SQL;

SELECT

DATEPART(SMFTIME) AS SYSTEM_DATE FORMAT DATE 11.1,

QWHSSSID AS SSID,

SUM(QPACTJST) AS CPU_TIME,

COUNT(*)

FROM DB2ACCTP

WHERE QWHSSSID = 'DB2A'

GROUP BY SSID, SYSTEM_DATE

;

Re: How to Sum Vertically

Kurt_Bremser — Wed, 18 Apr 2018 07:13:02 GMT

Since we've left the stone age of computing and the beasts understand lowercase perfectly well, there's no need anymore to keep capslock on in programming, which makes code easier for the eyes.

With some made-up data:

data db2acctp;
input smftime :e8601dt16. qwhsssid $ qpactjst;
cards;
2018-04-09T16:30 DB2A 3
2018-04-09T16:40 DB2A 4
2018-04-09T16:50 DB2A 2
2018-04-10T08:31 DB2A 2
2018-04-10T08:40 DB2A 3
;
run;

proc sql;
select
  datepart(smftime) as system_date format yymmddd10.,
  qwhsssid as ssid,
  sum(qpactjst) as cpu_time,
  count(*) as count
from db2acctp
where qwhsssid = 'DB2A'
group by ssid, system_date
;
quit;

Result:

system_date  ssid      cpu_time     count
-----------------------------------------
 2018-04-09  DB2A             9         3
 2018-04-10  DB2A             5         2

See my footnotes for preparing example data and posting code.

Re: How to Sum Vertically

alterman1 — Thu, 19 Apr 2018 00:27:05 GMT

Thanks Kurt. I appreciate the help.

Ron