topic Re: how to display housing and vehicle loan of a customer in the same year in SAS Health and Life Sciences

how to display housing and vehicle loan of a customer in the same year

dash — Sat, 18 Dec 2010 08:43:30 GMT

how to display housing(H) and vehicle loan(V) of a customer who had taken in the same year?
table is like that
data cust_loan;
input accno loanno $ year loan_amount;
cards;
110010 H023 2006 400000
110126 H024 2006 410000
110278 H045 2006 420000
112345 H046 2007 430000
110345 H056 2007 440000
110023 V001 2006 45000
110126 V002 2006 60000
110126 V002 2007 60000
110345 V052 2007 60400
110127 V512 2007 60078
;

I have got result for cust who had taken both housing and vehicle loan but unable to get the result in the same year. plz check
proc sql;
select * from cust_loan where loanno like 'H%' and accno in (select accno from cust_loan
where loanno like 'V%')
UNION ALL
select * from cust_loan where loanno like 'V%' and accno in(select accno from cust_loan
where loanno like 'H%');
quit;

Re: how to display housing and vehicle loan of a customer in the same year

Doc_Duke — Sat, 18 Dec 2010 20:12:44 GMT

you could use the "old" data step approach.

Use your SQL code to get people with both vehicle and home loans. This would get it down to people with multiple loans in one year:

PROC SORT; by year accno loanno; RUN;

DATA MultLoans;
SET;
BY year accno;
IF first.accno & last.accno THEN DELETE;
RUN;

This would have a few false positives (two cars in one year and house in another), but you could use a similar data step to winnow it further.

Re:

dash — Sun, 19 Dec 2010 04:49:24 GMT

thanks sir, but is it possible to write the same prog in proc sql

Re:

SPR — Mon, 20 Dec 2010 21:35:54 GMT

Hello Dash,

This is a possible solution:
[pre]
proc SQL;
create table r as select *
from cust_loan
group by year,accno
having count(*) > 1
order by year,accno
;quit;
[/pre]
Sincerely,
SPR