Help using Base SAS procedures

proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

proc sql

 

Hi All,

 

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

 

 

Capture.PNG


Accepted Solutions
Solution
‎07-31-2017 11:14 AM
Super User
Super User
Posts: 7,970

Re: proc sql

Posted in reply to shivamarrora0
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


All Replies
Super User
Super User
Posts: 7,970

Re: proc sql

Posted in reply to shivamarrora0

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;
Contributor
Posts: 39

Re: proc sql


data test;
input id marks;
cards;
1 10
2 20
3 30
4 40
;
Solution
‎07-31-2017 11:14 AM
Super User
Super User
Posts: 7,970

Re: proc sql

Posted in reply to shivamarrora0
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;
Contributor
Posts: 39

Re: proc sql

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
Super User
Super User
Posts: 7,970

Re: proc sql

Posted in reply to shivamarrora0

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.  

Contributor
Posts: 39

Re: proc sql

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;

Super User
Super User
Posts: 7,970

Re: proc sql

Posted in reply to shivamarrora0

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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