BookmarkSubscribeRSS Feed
dash
Obsidian | Level 7
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;
3 REPLIES 3
Doc_Duke
Rhodochrosite | Level 12
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.
dash
Obsidian | Level 7
thanks sir, but is it possible to write the same prog in proc sql
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 3 replies
  • 1150 views
  • 0 likes
  • 3 in conversation