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

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,

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
DavidGhan
SAS Employee

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

3 REPLIES 3
DavidGhan
SAS Employee

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;

lhyyy
Calcite | Level 5

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.

 

 

 

 

lhyyy
Calcite | Level 5

Thank you. 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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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