BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Solph
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

6 REPLIES 6
Reeza
Super User

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);

 

 

Solph
Pyrite | Level 9

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.

Reeza
Super User

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. 

SASKiwi
PROC Star

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....

Solph
Pyrite | Level 9

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.

ballardw
Super User

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

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!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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