SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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,

 

 

 


Accepted Solutions
Solution
‎03-15-2017 03:36 PM
SAS Employee
Posts: 21

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

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;

View solution in original post


All Replies
Solution
‎03-15-2017 03:36 PM
SAS Employee
Posts: 21

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

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;

New Contributor
Posts: 3

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

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.

 

 

 

 

New Contributor
Posts: 3

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

Thank you. It works.

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 255 views
  • 1 like
  • 2 in conversation