how to calculate the difference for row data

[Ram4sas]

Hi All,

I have below  table.

Cust_No	Cust_cntry	Date	total amount	old balance	banalce
1001	IN	4/30/2012	10,000.00	2,000.00	8000
1001	IN	4/30/2012	10,000.00	2,000.00	8000
1002	UK	4/31/2012	15,000.00	3,000.00	12000
1002	UK	4/31/2012	15,000.00	3,000.00	12000

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

Cust_No	Cust_cntry	Date	total amount	old balance
1001	IN	4/30/2012	10,000.00	2,000.00	8000
1001	IN	4/30/2012	10,000.00	2,000.00	8000
1001			0.00	0.00	0.00
1002	UK	4/31/2012	15,000.00	3,000.00	12000
1002	UK	4/31/2012	15,000.00	2,000.00	13000
1002			0.00	0.00	-1000

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

[Reeza]

How are you calculating the last row?

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

[RW9]

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.

[Reeza]

What about the diff or lag function instead?

[Ram4sas]

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_No	Cust_cntry	Date	total amount	old balance	Balance	Diff
1001	IN	4/30/2014	10,000.00	2,000.00	8000
1001	IN	4/30/2014	10,000.00	2,000.00	8000
1001			0.00	0.00	0.00	Equal
1002	UK	4/31/2014	15,000.00	3,000.00	12000
1002	UK	4/31/2014	15,000.00	2,000.00	13000
1002			0.00	0.00	-1000	Under

[RW9]

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;

[Ram4sas]

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_No	Cust_cntry	Date	total amount	old balance	Balance	Diff	Other
1001	IN	4/30/2014	10,000.00	2,000.00	8000
1001	IN	4/30/2014	10,000.00	2,000.00	8000
1001			0.00	0.00	0.00	Equal	0.00
1002	UK	4/31/2014	15,000.00	3,000.00	12000		300
1002	UK	4/31/2014	15,000.00	2,000.00	13000		200
1002			0.00	0.00	-1000	Under	100

Thank you in Advance..

[RW9]

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;

[Ram4sas]

HI RW9,

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

[Ram4sas]

  

ID	Type	date	amount
878	1	3/12/1999
983	1	3/2/1987
999	1	31/3/1990
1001	1	4/30/2014	10000
1001	2	4/30/2014	10000
1001			0
1002	1	4/31/2014	15000
1002	2	4/31/2014	15000
1002			0
1003	1		0
1003	2
2001	1	4/30/2014	0
3001	1	4/30/2014	0

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

1001	1	4/30/2014	10000
1001	2	4/30/2014	10000
1001			0
1002	1	4/31/2014	15000
1002	2	4/31/2014	15000
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.

[mfab]

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 🙂

[Kurt_Bremser]

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.