Hi All,
I need the below output using proc sql... Please help
data test; input id marks; cards; 1 10 2 20 3 30 4 40 ; run; proc sql; create table WANT as select *, (select sum(MARKS) from TEST where ID <= A.ID) as TOTAL from TEST A; quit;
Why do you need to use SQL? Datastep is far better suited to this. Untested code here, post test data in the form of a datastep as text in a code window ({i} above post)!!
proc sql; create table WANT as select *, (select sum(MARKS) from TOTAL where ID <= A.ID) as TOTAL from TEST A; quit;
data test; input id marks; cards; 1 10 2 20 3 30 4 40 ; run; proc sql; create table WANT as select *, (select sum(MARKS) from TEST where ID <= A.ID) as TOTAL from TEST A; quit;
Its called a subquery. A.ID is from the outer query, the sub query is getting all records with ID less than the row from A and summing them up.
It is not being "grouped" it is a sub-query. So one first iteration, the subquery sums() up everything with an id < 10 and returns that, then next iteration id <20 returns that on and on. You may want to have a readup on SQL and how it works.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.