topic Re: Sum up a column in SAS Proc SQL with condition like (ID < = ID) in SAS Data Management

Sum up a column in SAS Proc SQL with condition like (ID < = ID)

lhyyy — Wed, 15 Mar 2017 18:31:24 GMT

hi everyone,

Wondering what I can do to sum the following table

ID Amount

1 1

2 1

3 1

4 1

5 1

6 1

where ID < = ID

result shoud be

ID Aggregated amount

1 1

2 2

3 3

4 4

5 5

6 6

Is there any way I can do that?

thank you,

Re: Sum up a column in SAS Proc SQL with condition like (ID < = ID)

DavidGhan — Wed, 15 Mar 2017 18:39:52 GMT

If I understand what you wish to do, then this should accomplish that.

I created the source table you describe in the DATA step. The PROC SQL step performs a self join on that table and does the group summarization of that result:

data new;

do id=1 to 6;

amount=1;

output;

end;

run;

proc print data=new;

run;

proc sql;

select a.id, sum(a.amount) as aggregateamount

from new as a,new as b

where a.id >= b.id

group by a.id;

quit;

Re: Sum up a column in SAS Proc SQL with condition like (ID < = ID)

lhyyy — Wed, 15 Mar 2017 19:13:17 GMT

Hi David,

thank you for your response.

I just did a test.

then i ran

proc sql;

select a.id, sum(a.amount) as aggregateamount

from new as a,new as b

where a.id >= b.id

group by a.id;

quit;

However,

the result is not what i expected.

Re: Sum up a column in SAS Proc SQL with condition like (ID < = ID)

lhyyy — Wed, 15 Mar 2017 19:36:56 GMT

Thank you. It works.