latest 2 observation from each group using only PROC SQL

[Aman4SAS]

Hi All, 

I need to find latest 2 observation/transaction  from each group using only PROC SQL.

I have tried to make my code, Kindly suggest if any improvization available:

Problem in my code is if i require latest 3 obs then i will need increase steps to get result.

Code below:

proc sql;
create table new1 as
select distinct * from test group by product having transaction_date =max(transaction_date)
union
select distinct * from
(select * from test except
(select distinct * from test group by product having transaction_date =max(transaction_date)))
group by product having transaction_date =max(transaction_date) ;

quit;

: Xia Keshan need help.

[RW9]

That will prove very complicated.  SQL works based on logical selection of data, not by random position in table.  To do this kind of thing you would first need to add an equivalent of _N_ to the data, so that SQL van logically take the max(_N_) and max(_N_)-1 obs from each group.  The question really is why bother, its far easier with a datastep, use the right tool for the right job - programming 101.

[Damien_Mather]

the sql monotone() function is supported. select .,.,., monotone(date) as sequence, .,.,. would create an _n_ - like field.

[Reeza]

I don't believe it's supported.

http://support.sas.com/techsup/notes/v8/15/138.html

@Damien_Mather  Do you have something that indicates otherwise?

[Damien_Mather]

Maybe you're right. Mainly I do follow the rules, but sometimes when I'm feeling a bit naughty I'll go crazy and try some tempting function I've seen in someone else's code examples in my own environments to see if they work regardless of what the documentation says. Don't tell Dr. Jim G., though, I think he thinks I'm an OK guy.

[RW9]

You would also need to be careful using that function.  As I stated above, SQL has no real concept of record position, it can only pull out data as a logicl argument.  Monotonic() could return two different results depending on system setup, SQL options used, and various sortings and groupings.

[Reeza]

Why only SQL? 

[Ksharp]

HoHo. That is really not easy for SAS SQL.

But easy for Data step.

Or if you have index variable like:

n sex

1 F

2 F

1 M

2 M

3 M

that maybe would be easy as

select distinct * from test group by product having n  between max(n)-2 and max(n)

[Aman4SAS]

Thanks for all reply. 

As suggested by RW , i tried to delete this post but i was not able to delete.

I required only in SQL bcaz i know all other option to get result i.e. data step, merge but i have been asked to do it in SQL and my answer was same as RW Sir, and result, i am failed. so to be confirmed i have asked this to you legend people.

Thanks Xia Keshan for your attention on my question.

Please suggest me how can i delete this post or do i need to keep it as it is. 

[Patrick]

I don't see any need to delete your post. Also "don't do it this way" answers are valuable.

IF your data is stored in a database then the answer could be very well to use SQL; but you need to tell us which database and which volumes (like: you've got 100 million transactions but the expected result set will only be 100'000 thousand).

Depending on the database there can be native SQL language extensions which will make it much easier to write the required logic in SQL (like analytic functions).

[Ksharp · Accepted solution]

Ha,

Hope it is not too last. I figure out one way.

data have;
input id date : date9. x;
format date date9.;
cards;
1 1jan2010 1
1 2jan2010 2
1 2jan2010 32
1 4feb2010 45
1 4feb2010 34
1 4dec2010 45
1 4dec2010 34
2 1jan2010 1
2 2jan2010 2
2 3jan2010 32
2 14feb2010 45
2 24feb2010 34
;
run;
proc sql;
select *,
(select count(*) from (select distinct id,date from have) as b
 where b.id=a.id and
  b.date between a.date and 
  (select max(date) from have where id=a.id)
) as count
from have as a
where calculated count in (1 2 3);
quit;

[Aman4SAS]

Thanks a lot. 

It works.

Connecting with you always return me learn something new. Its Great . Thanks once again.