## how to calculate the difference for row data

Frequent Contributor
Posts: 90

# how to calculate the difference for row data

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?

Super User
Posts: 23,771

## 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
Posts: 9,599

## 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: 23,771

## 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_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

Super User
Posts: 9,599

## 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_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..

Super User
Posts: 9,599

## 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

 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.

Frequent Contributor
Posts: 131

## 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: 10,279

## 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
How to convert datasets to data steps
How to post code
Discussion stats
• 11 replies
• 615 views
• 1 like
• 5 in conversation