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
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?
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"
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.
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 |
Okay, thank you. Now show us the desired output from this.
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 |
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;
No it wasn't giving any error but it wasn't creating any data too.In the output there was no data.
@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:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.