PROC SQL - Running sum/total for a range

[Ranny]

Wowh, this is great and this should work for me, quick question - can this be done in SELECT statement instead of FROM statement? is it possible?

[novinosrin]

Please choose @FreelanceReinh Sir's code ( he know who and why i call a  few sir 🙂 coz his is much better by any means

[Ranny]

This is great and quick question, Can this step (all calculation) be done in SELECT statement instead of FROM statement ?

[FreelanceReinh]

---

@Ranny wrote:

This is great and quick question, Can this step (all calculation) be done in SELECT statement instead of FROM statement ?

---

Do you mean without a subquery?

Yes, I think so:

proc sql;
create table want as
select a.*, case when count(b.month)>=12 & a.month<24 then sum(b.weight)
                 else .
            end as sumwgt12
from input a left join input b
on a.month<=b.month<a.month+12
group by 1,2,3,4,5,6
order by a.month;
quit;

[Ranny]

Sorry, I should have explained it better,

I meant to say

proc sql;
create table want as
select c.*, sumwgt12

    (All the calculation for the sum )
from input 
quit;

[novinosrin]

Hi @Ranny Do you mean you want an output in "one pass" of sql? meaning no join, subquery whatsoever like that? hmm

[Ranny]

Yes exactly 

[FreelanceReinh]

proc sql;
create table want as
select c.*,
  (select case when count(a.month)=12 & c.month<24 then sum(a.weight)
               else .
          end
   from input a
   where c.month<=a.month<c.month+12) as sumwgt12
from input c;
quit;

Like this?

(Edit: minor simplification: "=12" instead of ">=12")

Edit 2: The alias "a" in the subquery is redundant. So, the code could be simplified to:

proc sql;
create table want as
select c.*,
  (select case when count(month)=12 & c.month<24 then sum(weight)
               else .
          end
   from input
   where c.month<=month<c.month+12) as sumwgt12
from input c;
quit;

[Ranny]

Yes, Thanks

This is giving an error

ERROR: Column month could not be found in the table/view identified with the correlation name C.
ERROR: Column month could not be found in the table/view identified with the correlation name C.
ERROR: Column month could not be found in the table/view identified with the correlation name C.
ERROR: The following columns were not found in the contributing tables: month.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

[FreelanceReinh]

Impossible. I used your input dataset, which can also be written as

data input;
length sex $8;
set sashelp.class
    sashelp.class(firstobs=13 obs=17)
    sashelp.class(obs=4)
    sashelp.class(firstobs=5 obs=11)
    sashelp.class(obs=2);
month=_n_-1;
run;

Please note that your input dataset does contain a variable month, contrary to what your error messages state. So, please correct your code.

[novinosrin · Accepted solution]

Slight improvement to my previous. Yet still violates your rules  by a mile. 

data have;
input Name $	Sex $	Age	Height	Weight	Time_month;
cards;
Alfred	M	14	69	112.5	0
Alice	F	13	56.5	84	1
Barbara	F	13	65.3	98	2
Carol	F	14	62.8	102.5	3
Henry	M	14	63.5	102.5	4
James	M	12	57.3	83	5
Jane	F	12	59.8	84.5	6
Janet	F	15	62.5	112.5	7
Jeffrey	M	13	62.5	84	8
John	M	12	59	99.5	9
Joyce	F	11	51.3	50.5	10
Judy	F	14	64.3	90	11
Louise	F	12	56.3	77	12
Mary	F	15	66.5	112	13
Philip	M	16	72	150	14
Robert	M	12	64.8	128	15
Ronald	M	15	67	133	16
Thomas	M	11	57.5	85	17
William	M	15	66.5	112	18
Louise	F	12	56.3	77	19
Mary	F	15	66.5	112	20
Philip	M	16	72	150	21
Robert	M	12	64.8	128	22
Ronald	M	15	67	133	23
Alfred	M	14	69	112.5	24
Alice	F	13	56.5	84	25
Barbara	F	13	65.3	98	26
Carol	F	14	62.8	102.5	27
Henry	M	14	63.5	102.5	28
James	M	12	57.3	83	29
Jane	F	12	59.8	84.5	30
Janet	F	15	62.5	112.5	31
Jeffrey	M	13	62.5	84	32
John	M	12	59	99.5	33
Joyce	F	11	51.3	50.5	34
Alfred	M	14	69	112.5	35
Alice	F	13	56.5	84	36
;
proc sql;
create table want(drop=t Time_month_) as
select a.*,b.Time_month as Time_month_,b.Time_month<=a.Time_month<=b.Time_month+11 as t,sum(a.weight)*(calculated t=1) as want
from have a, have b
group by b.Time_month,t
having time_month=min(time_month) and t=1
order by  b.Time_month,a.time_month;
quit;

[Ranny]

if you can please explain what this step is doing? "t" calculation, sum calculation and condition in the group/having by

[novinosrin]

The process involves a Cartesian product n*m rows in the objective of identifying the records for running total - /*this in itself is a violation of your rule plus Cartesian making it less efficient*/

Then b.Time_month<=a.Time_month<=b.Time_month+11 as t results in a boolean value 1/0 when true/false for the expression. We need to  sum where  the expression is  true for each timemonth.

b.Time_month forms the parent group(the parentgroup will hold values 0,1..12) and t forms the sub group 0,1 with 1's being our interest.

Since we need the sum as output for for each record we use the having clause get the first record in that group.

[Tom]

I don't understand why you need it as SQL code. The only reason you would need SAS program to use SQL is if you were pushing the SQL code into a remote database. And if you are doing that then you need to ask the question on a forum that supports that databases implementation of SQL.  Perhaps they support window functions?

[Ranny]

Sorry to keep pushing on SQL, As the entire process is build in SQL (running in SAS) - some SQL programmer must have created this long back, as I am not allowed to change production job, I was assign to this to modify the task, so I would also like to keep the SQL query as it is and I agree with you, probably it is time for us to convert this process from SQL to SAS. I appreciate all of your help and support here. This is a great community.