comparison between different columns in different rows

[rohithverma]

i have a input like

sno  loanno  debit      credit      cheqno

1      1234     200         0                222

2      1234       0           200            222

Here we have to check whether the debit amount is equal to credit amount based on the cheqno only and if it is equal 

the o/p should be like 

sno  loanno  debit      credit      cheqno     x

1      1234     200         0                222      2

2      1234       0           200            222      1

[PaigeMiller]

This is a very limited example; are all the rows paired as you show with debit in row 1 and credit in row 2? It's hard to generalize from one example.

How do you arrive at the values of X?

[rohithverma]

No, all the rows won't be like this but in between for some of the cheque id's the debit amount and credit are present as i mentioned in the question .So here the comparison should be made between the two rows and if debit is equal to credit then  those two records should fetch in the output .Please ignore variable "X"

[Reeza]

I think you'll need to provide more sample data, otherwise it's very likely any solution provided will only work on the case above. Try and include a case for each possible outcome you need to consider.

[PaigeMiller]

Is there always exactly one credit and exactly one debit for each cheque?

Could there be more than one credit or more than one debit for each cheque? Could there be zero credit lines or zero debit lines for a cheque?

Agreeing with @Reeza, this is not enough data to write working code, and we need a larger data set that covers all possible cases of what might happen.

[rohithverma]

These are all the possible cases in the data as mentioned below .But here we need to consider only for which the cheque id's are equal for subsequent records should be considered along with that the debit amount should be equal to credit amount and if there are any open accounts like sno 5 & 6 for those the key variable will be loan no. For remaining we need to consider the cheque ids 

sno	loan	dbamt	cramt	cheque
1	1234	200	0	222
2	1234	0	200	222
3	1234	200	0	222
4	1234	0	200	222
5	1456	300	0	333
6	1389	400	0	444
7	7890	200	0	345
8	7890	0	200	345
9	7890	200	0	456

[PaigeMiller]

Okay, thank you. Now show us the desired output from this.

[rohithverma]

So, now the desired output is 

loan	dbamt	cramt	cheque
1234	200	0	222
1234	0	200	222
1234	200	0	222
1234	0	200	222
7890	200	0	345
7890	0	200	345

[Patrick]

Not sure that I fully understood but if your sample data really covers all the cases then below will give you the desired output as shown.

data have;
  infile datalines truncover dlm='|';
  input sno loan dbamt cramt cheque $;
  datalines;
1|1234|200|0|222
2|1234|0|200|222
3|1234|200|0|222
4|1234|0|200|222
5|1456|300|0|333
6|1389|400|0|444
7|7890|200|0|345
8|7890|0|200|345
9|7890|200|0|456
;

proc sql;
/*  create table maybe_want as*/
  select *
  from have
  group by cheque
  having sum(dbamt)=sum(cramt)
  order by sno
  ;
quit;

[rohithverma]

No it wasn't giving any error but it wasn't creating any data too.In the output there was no data.

[Patrick]

---

@rohithverma wrote:

No it wasn't giving any error but it wasn't creating any data too.In the output there was no data.

---

If you want the code to create a table then un-comment the Create Table statement in the SQL.

The code I've posted executed as-is will create below report: