Please choose @FreelanceReinh Sir's code ( he know who and why i call a few sir 🙂 coz his is much better by any means
This is great and quick question, Can this step (all calculation) be done in SELECT statement instead of FROM statement ?
@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;
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;
Hi @Ranny Do you mean you want an output in "one pass" of sql? meaning no join, subquery whatsoever like that? hmm
Yes exactly
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;
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.
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;
if you can please explain what this step is doing? "t" calculation, sum calculation and condition in the group/having by
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.
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.