BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shivamarrora0
Obsidian | Level 7

 

Hi All,

 

I need the below output  using proc sql... Please help

 

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26
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;

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
shivamarrora0
Obsidian | Level 7

data test;
input id marks;
cards;
1 10
2 20
3 30
4 40
;
RW9
Diamond | Level 26 RW9
Diamond | Level 26
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;
shivamarrora0
Obsidian | Level 7
If you dont mind can you please explain this query, how (select sum(MARKS) from TEST where ID <= A.ID) as TOTAL
from TEST A;


is working. Please its a request ...

because i am unable to figure out how the id is comparing to a.id ..because we are not using the self join here
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

shivamarrora0
Obsidian | Level 7
i wrote the below query and its working fine but i have compared and the group by them..But in your query how it is getting grouped ...






proc sql;
select distinct test.marks,test.id ,sum(a.marks) as total from test,test as a where a.marks le test.marks group by test.id;
quit;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1134 views
  • 1 like
  • 2 in conversation