topic Re: comparison between different columns in different rows in New SAS User

comparison between different columns in different rows

rohithverma — Wed, 22 Jan 2020 17:30:59 GMT

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

Re: comparison between different columns in different rows

PaigeMiller — Wed, 22 Jan 2020 17:36:54 GMT

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?

Re: comparison between different columns in different rows

rohithverma — Wed, 22 Jan 2020 17:43:09 GMT

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"

Re: comparison between different columns in different rows

Reeza — Wed, 22 Jan 2020 17:52:16 GMT

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.

Re: comparison between different columns in different rows

PaigeMiller — Wed, 22 Jan 2020 17:56:47 GMT

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.

Re: comparison between different columns in different rows

rohithverma — Wed, 22 Jan 2020 18:06:06 GMT

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

Re: comparison between different columns in different rows

PaigeMiller — Wed, 22 Jan 2020 18:33:23 GMT

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

Re: comparison between different columns in different rows

rohithverma — Thu, 23 Jan 2020 02:45:29 GMT

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

Re: comparison between different columns in different rows

Patrick — Thu, 23 Jan 2020 05:39:25 GMT

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;

Re: comparison between different columns in different rows

rohithverma — Thu, 23 Jan 2020 06:16:46 GMT

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

Re: comparison between different columns in different rows

Patrick — Thu, 23 Jan 2020 23:27:58 GMT

@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: