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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.