topic First-proc sql in SAS Programming

First-proc sql

Ronein — Sat, 12 Jun 2021 09:41:49 GMT

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;

Re: First-proc sql

Ksharp — Sat, 12 Jun 2021 11:38:00 GMT

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;

Re: First-proc sql

tarheel13 — Sat, 12 Jun 2021 18:48:07 GMT

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.