topic Re: proc sql in SAS Procedures

proc sql

shivamarrora0 — Mon, 31 Jul 2017 10:19:04 GMT

Hi All,

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

Re: proc sql

RW9 — Mon, 31 Jul 2017 10:36:13 GMT

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;

Re: proc sql

shivamarrora0 — Mon, 31 Jul 2017 12:50:42 GMT

data test;
input id marks;
cards;
1 10
2 20
3 30
4 40
;

Re: proc sql

RW9 — Mon, 31 Jul 2017 12:55:24 GMT

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;

Re: proc sql

shivamarrora0 — Mon, 31 Jul 2017 13:14:09 GMT

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

Re: proc sql

RW9 — Mon, 31 Jul 2017 13:29:24 GMT

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.

Re: proc sql

shivamarrora0 — Mon, 31 Jul 2017 15:16:25 GMT

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;

Re: proc sql

RW9 — Mon, 31 Jul 2017 15:55:37 GMT

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.