DATA Step, Macro, Functions and more

Finding first (or last) record using SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Finding first (or last) record using SQL

I typically use first. and last. in data step to select the first (or last) recordd within an ID. It is straightorward in SAS data step but not in SQL. I tried to use something like Top 1 and OUTER APPLY, from my Google search, but doesn't quite work.

 

To avoid the complexity, I'll use one dataset as an example: a list of ID and their spending amount by month. I just want to find out the amount from the latest month. I'm hoping to apply code as terse as possible (before I start to build for complex analysis). 

 

Here is some sample data.

data have; input id month amount;
datalines;
1 11 15
1 4 20
2 5 40
2 7 35
2 2 59
;
proc print; run;


proc sql; create table want
as select id, month, amount
from have
order by id, month descending;
quit;
proc print; run;

 

And from here I'd like to find the first record for the amount from the latest month. (I could combine in one step.)

 

So the data I want is
ID Amount
1 15
2 35

 

This qustion must have been asked to death from people who are forced to do the same job using SQL So please bear with me. Many thanks.


Accepted Solutions
Solution
‎09-06-2016 09:30 PM
Super User
Posts: 3,106

Re: Finding first (or last) record using SQL

I'm a firm believer in using the right SAS tool for the job, in this case the DATA step.

 

A house builder doesn't use a hand saw when a power saw will do the job quicker and more accurately....

View solution in original post


All Replies
Super User
Posts: 17,831

Re: Finding first (or last) record using SQL

SQL doesn't have a concept. 

You have to have another field to limit your query on and then using a having clause. 

I think you may also need multiple queries for first/last depending on what you're trying to do.

 

Does your SQL sort make sense in the results you want? It looks like you want the minimum amount so search for min(amount), but the sort should be id, month, desc amount?

You basically have to re-think your logic in terms of aggregate functions. 

 

ie 

 

group by <grouping variables (by variables in datastep)>

having field_limit=min(field_limit);

 

 

Frequent Contributor
Posts: 101

Re: Finding first (or last) record using SQL

This is probably a bad example. It's a coincidence that it gives the same results using min. But it's not always the case. The actual example (still involving only one dataset) sort of is I've a list of IDs and their addresses get recorded (vertically) each time (date) they return to register and it's possible they'd provide two addresses on the same date (thus different address type). I'll need to find out address on the latest date (date descending) and if there are two addresses on the same date, I'd go address type in descending order (e.g. R=residential address > B=business address). I typically proc sort / noupkey to do the work, or sort in descending order and use first. to do it. So it looks like I'd just have to go back to Data Step to do the work. Thanks.

Super User
Posts: 17,831

Re: Finding first (or last) record using SQL

Not necessarily but it's one of the powers of a SAS data step. Your minimum/maximum may be the date in this case and/or the type. 

Solution
‎09-06-2016 09:30 PM
Super User
Posts: 3,106

Re: Finding first (or last) record using SQL

I'm a firm believer in using the right SAS tool for the job, in this case the DATA step.

 

A house builder doesn't use a hand saw when a power saw will do the job quicker and more accurately....

Frequent Contributor
Posts: 101

Re: Finding first (or last) record using SQL

Thanks for confirming - using Data Step. I've always hope to find quivalent (concise) code to do in SQL, so I'm glad to hear experts' take on it and search no more. (FYI when our workplace server is switched to Oracle, we are strongly urged to use SQL if possible, to take advantage of the speed it can provide, with running SQL There is incentive for it as it now takes much longer time to run Proc Sort in Oracle.

Super User
Posts: 10,500

Re: Finding first (or last) record using SQL

You could use the SQL to do ORDER BY before using the data step for First or Last processing.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 447 views
  • 1 like
  • 4 in conversation