Hello,
There is a data set of loans transcations with following fields: ID (customer ID), date (date laon opened), type (type of loan), sum(sum of loan).
I am trying to use proc sql techinque to select first row for each customer.
May anyone explain if this code is correct and also explain the order of statements executation in this code?
I know that therer is a subquery , may anyone expplain how SAS process this query?
Data Loans_Transactions;
Input ID date :date9. type sum;
cards;
1 17MAy2020 728 1000
2 12Mar2021 163 5000
3 18Jun2021 728 3000
4 03Jan2020 728 2000
5 07Sep2020 163 5000
1 09Sep2020 182 4000
2 27Aug2020 182 3000
6 04DEC2020 163 2000
1 05FEB2021 728 1000
3 08Apr2021 182 9000
;
Run;
proc sql;
create table first_bygroup As
select ID,date,type,sum
FROM Loans_Transactions as a
Where ID=(select min(date )
FROM Loans_Transactions as b
WHERE a.date=b.date)
Order by ID
;
quit;
Try this one . It is very readable .
Data Loans_Transactions; Input ID date :date9. type sum; format date date9.; cards; 1 17MAy2020 728 1000 2 12Mar2021 163 5000 3 18Jun2021 728 3000 4 03Jan2020 728 2000 5 07Sep2020 163 5000 1 09Sep2020 182 4000 2 27Aug2020 182 3000 6 04DEC2020 163 2000 1 05FEB2021 728 1000 3 08Apr2021 182 9000 ; proc sql; create table want as select * from Loans_Transactions group by id having date=min(date); quit;
Did you try running your code? It makes an empty table. For subqueries, you should work from the inside out. SAS will do the innermost query first. Ksharp's code is less lines but this is equivalent:
proc sql;
select * from loans_transactions
where date in
select date from
(select ID, min(date) as date
from loans_transactions
group by ID);
quit;
Do you see that I wrote the code in parentheses first? First, I get the earliest date by ID to create 1 row per ID. Then I select the date from those rows and finally I get all columns from loans_transactions where date is in the earliest date per ID. I recommend running each query individually to see what it creates.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: