topic Re: Summing some rows for each ıd in SAS Enterprise Guide

Summing some rows for each ıd

Kdumlu — Sat, 24 Oct 2020 13:10:57 GMT

Hello everyone,

I have data that all customers have different periods of payment. I have colomns for remaining periods and payments. I need to create colomn(a) and sum rows based on period.

For example, the customer has 10 period payment. I have 10 rows for that customer. For period 1 I have to sum all rows of payment and for period 2 I have to sum first 9 rows. (Basically I have to sum rows for remaining_period-period)

Also, I need to get value of specific row. For example, the customer that I mention above. If I am in period 1, I need to get 10th row value from another colomn. For period 2 I need to get 9th row value etc.

Thanks for your help.

Re: Summing some rows for each ıd

PaigeMiller — Sat, 24 Oct 2020 13:20:18 GMT

Can you show us a small but realistic example?

Re: Summing some rows for each ıd

Kdumlu — Sat, 24 Oct 2020 13:30:07 GMT

For example for that customer, for period 1 I need to sum first 9 row of E and get 10th row of C. After that, sum these values. For period 2, values should be first 8 row of E and 9th row of C.

Remember each customer has different number of period.

Thanks for help.

Re: Summing some rows for each ıd

PaigeMiller — Sat, 24 Oct 2020 13:35:32 GMT

Sorry, my bad. We need data that we can use when we write sample code, and we can't really use screen captures. Here are instructions to turn your SAS data set into SAS code that we can start from.

Re: Summing some rows for each ıd

ballardw — Sat, 24 Oct 2020 16:53:16 GMT

@Kdumlu wrote:

For example for that customer, for period 1 I need to sum first 9 row of E and get 10th row of C. After that, sum these values. For period 2, values should be first 8 row of E and 9th row of C.

Remember each customer has different number of period.

Thanks for help.

What is a "period"? You don't have a variable with that name, and most of the variable names are truncated so can't even make much of a guess?

What rule, as in values in the data set, let us know that you need to sum "first 9 row of E" or "first 8" for period 2? We may be able to do specific examples but without a generic rule and how to use it you will be back asking about period 3 or some such.

Since column C already apparently has values, are we replacing just one value or is something to be done with other values of C?

And does this actually need to be a change int he data set? If people are the only ones that need to see something then it may be a report and possibly can be done without modifying a data set. Having summary values inside data sets on only some rows can lead to serious misuse of data if not extremely carefule.

Re: Summing some rows for each ıd

FreelanceReinh — Sun, 25 Oct 2020 19:33:49 GMT

Hello @Kdumlu,

I've created a dataset HAVE which looks very similar to your sample data with regard to variables C and E. Can you (manually) create a similar dataset WANT which contains the additional variable(s) you want to derive?

data have(drop=f i);
retain f 1.08 period C E;
do i=0 to 9;
  C=195.4*f**-(i/2);
  E=7817.292*f**-(i/2);
  period=i+1;
  output;
end;
format C E commax10.2;
run;

Result:

period      C          E

   1      195,40    7.817,29
   2      188,02    7.522,19
   3      180,93    7.238,23
   4      174,10    6.964,99
   5      167,52    6.702,07
   6      161,20    6.449,07
   7      155,11    6.205,62
   8      149,26    5.971,36
   9      143,62    5.745,94
  10      138,20    5.529,04

If your real C and E values follow similarly systematic patterns (see formulas in the code), both the sums of E values and the selected C value could possibly be derived without reading all individual values.