how to calculate the difference for row data

Reply
Frequent Contributor
Posts: 90

how to calculate the difference for row data

Hi All,

I have below  table.

Cust_NoCust_cntryDatetotal amountold balancebanalce
1001IN4/30/201210,000.002,000.008000
1001IN4/30/201210,000.002,000.008000
1002UK4/31/201215,000.003,000.0012000
1002UK4/31/201215,000.003,000.0012000

But i want develop this output. how to get this...

Cust_NoCust_cntryDatetotal amountold balance
1001IN4/30/201210,000.002,000.008000
1001IN4/30/201210,000.002,000.008000
1001 0.000.00        0.00
1002UK4/31/201215,000.003,000.0012000
1002UK4/31/201215,000.002,000.0013000
1002 0.000.00-1000

could i request all to suggest how to get this output?

Super User
Posts: 19,789

Re: how to calculate the difference for row data

How are you calculating the last row?

If it was straight totals Proc Tabulate/Print or Report would work.

Super User
Super User
Posts: 7,944

Re: how to calculate the difference for row data

Hi,

Well, for two values its real simple with first/last and by:

data have;
  attrib Cust_No format=8. Cust_cntry Datetotal format=$10. amount old balance format=8.;
  infile datalines;
  input cust_no cust_cntry $ datetotal $ amount old balance;
datalines;
1001 IN 4/30/2012 10000.00 2000.00 8000
1001 IN 4/30/2012 10000.00 2000.00 8000
1002 UK 4/31/2012 15000.00 3000.00 12000
1002 UK 4/31/2012 15000.00 2000.00 13000
;
run;

data want;
  set have;
  by cust_no;
  retain lstamnt lstold lstbal;
  if first.cust_no then do;
    lstamnt=amount;
    lstold=old;
    lstbal=balance;
    output;
  end;
  else do;
    output;
    cust_cntry="";
    datetotal="";
    amount=lstamnt-amount;
    old=lstold-old;
    balance=lstbal-balance;
    output;
  end;
run;

For more rows you just need to have a third if statement to capture not first and not last.

Super User
Posts: 19,789

Re: how to calculate the difference for row data

What about the diff or lag function instead?

Frequent Contributor
Posts: 90

Re: how to calculate the difference for row data

Hi RW9,

its working fine. i want to add one more column in the last as called 'Diff'.based on Balance value value should be equal or Under if negative value.

Cust_NoCust_cntryDatetotal amountold balanceBalanceDiff
1001IN4/30/201410,000.002,000.008000
1001IN4/30/201410,000.002,000.008000
1001 0.000.000.00Equal
1002UK4/31/201415,000.003,000.0012000
1002UK4/31/201415,000.002,000.0013000
1002 0.000.00-1000Under

Reeza

Super User
Super User
Posts: 7,944

Re: how to calculate the difference for row data

Hi,

Just add an if statement in (note untested as no longer at work):

data want;

  set have;

     attrib diff format=$10.;

  by cust_no;

  retain lstamnt lstold lstbal;

  if first.cust_no then do;

    lstamnt=amount;

    lstold=old;

    lstbal=balance;

    output;

  end;

  else do;

    output;

    cust_cntry="";

    datetotal="";

    amount=lstamnt-amount;

    old=lstold-old;

    balance=lstbal-balance;

/****/

     if balance < 0 then diff="Under";

     else if balance=0 then diff="Equal";

     else diff="Over";

/****/

    output;

  end;

run;

Frequent Contributor
Posts: 90

Re: how to calculate the difference for row data

Hi,

I want to add extra column called 'Other' with difference values for that column values.

if data values available then it it has to hold the difference between those values as above.

if no data values then the difference should display as 0.00. like below.  how to get this output.

Cust_NoCust_cntryDatetotal amountold balanceBalanceDiffOther
1001IN4/30/201410,000.002,000.008000
1001IN4/30/201410,000.002,000.008000
1001 0.000.000.00Equal0.00
1002UK4/31/201415,000.003,000.0012000 300
1002UK4/31/201415,000.002,000.0013000 200
1002 0.000.00-1000Under100

Thank you in Advance..

Super User
Super User
Posts: 7,944

Re: how to calculate the difference for row data

You just need to look at adding if statements as appropriate (marked where I would put them below), logic for that I will leave to you:

data want;

  set have;

     attrib diff format=$10.;

  by cust_no;

  retain lstamnt lstold lstbal;

/* Insert your logic to work out that column, e.g. if something < something else then other=something-something else */

   if first.cust_no then do;

    lstamnt=amount;

    lstold=old;

    lstbal=balance;

    output;

  end;

  else do;

    output;

    cust_cntry="";

    datetotal="";

    amount=lstamnt-amount;

    old=lstold-old;

    balance=lstbal-balance;

     if balance < 0 then diff="Under";

     else if balance=0 then diff="Equal";

     else diff="Over";

    output;

  end;

run;

Frequent Contributor
Posts: 90

Re: how to calculate the difference for row data

HI RW9,

ok, thank you for your quick reply. its really helps me.

Frequent Contributor
Posts: 90

Re: how to calculate the difference for row data

  

IDTypedateamount
87813/12/1999
98313/2/1987
999131/3/1990

 

 

 

 
1001
14/30/201410000
100124/30/201410000
1001 0
100214/31/201415000
100224/31/201415000
1002 0
10031 0
10032
200114/30/20140
300114/30/20140

I want to get output only ID records which has type 1,2, and  with calculation row like below.


 

 

 

 
1001
14/30/201410000
100124/30/201410000
1001 0
100214/31/201415000
100224/31/201415000
1002 0

I have tried with this code

data out;

set source;

by id;

if first.id eq last.id then outout;

run;

this is not working and giving wrong output.

can you please guide me how to get the required output?

thank you.

Frequent Contributor
Posts: 126

Re: how to calculate the difference for row data

Not sure, if that is the question, but you could do:

data out;

set source;

where(type in (.,1,2));

run;

"." does specify the so called missing values you seem to have in your calculation row and the rest should be quite self-eplanatory :-)

Super User
Posts: 7,775

Re: how to calculate the difference for row data

first. only marks that you are reading the first record of a by group, last. does the same for the last record.

Your condition will therefore be true if the by group only has one record (wich is the first and last at the same time) or for records that are neither first or last of their respective by group.

What you probably want is to compare a value from the first record with that of the last. You willl need to RETAIN a variable:

data want1 (keep=id);

set have;

by id;

retain int_amount;

if first.id then int_amount=amount;

if last.id and amount = in_amount then output;

run;

Now you have a table of id's that represent those by groups where the amount in the last record is equal to the amount in the first record. When you merge that back with the original data set and keep only matching records, you get your wanted by groups.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 11 replies
  • 526 views
  • 1 like
  • 5 in conversation