Help using Base SAS procedures

latest 2 observation from each group using only PROC SQL

Accepted Solution Solved
Reply
Super Contributor
Posts: 266
Accepted Solution

latest 2 observation from each group using only PROC SQL

 

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.


Accepted Solutions
Solution
‎10-22-2016 07:37 AM
Super User
Posts: 10,046

Re: latest 2 observation from each group using only PROC SQL

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: latest 2 observation from each group using only PROC SQL

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.

Frequent Contributor
Posts: 136

Re: latest 2 observation from each group using only PROC SQL

the sql monotone() function is supported. select .,.,., monotone(date) as sequence, .,.,. would create an _n_ - like field.
Super User
Posts: 19,873

Re: latest 2 observation from each group using only PROC SQL

Posted in reply to Damien_Mather

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?

Frequent Contributor
Posts: 136

Re: latest 2 observation from each group using only PROC SQL

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.

Super User
Super User
Posts: 7,997

Re: latest 2 observation from each group using only PROC SQL

Posted in reply to Damien_Mather

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.

Super User
Posts: 19,873

Re: latest 2 observation from each group using only PROC SQL

Why only SQL? 

Super User
Posts: 10,046

Re: latest 2 observation from each group using only PROC SQL

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)

Super Contributor
Posts: 266

Re: latest 2 observation from each group using only PROC SQL

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. 

Respected Advisor
Posts: 4,173

Re: latest 2 observation from each group using only PROC SQL

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

 

Solution
‎10-22-2016 07:37 AM
Super User
Posts: 10,046

Re: latest 2 observation from each group using only PROC SQL

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;
Super Contributor
Posts: 266

Re: latest 2 observation from each group using only PROC SQL

Thanks a lot. 

 

It works.

 

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

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 457 views
  • 0 likes
  • 6 in conversation