topic Re: latest 2 observation from each group using only PROC SQL in SAS Procedures

latest 2 observation from each group using only PROC SQL

Aman4SAS — Fri, 21 Oct 2016 15:05:48 GMT

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.

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

RW9 — Fri, 21 Oct 2016 15:11:55 GMT

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.

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

Reeza — Fri, 21 Oct 2016 17:44:51 GMT

Why only SQL?

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

Ksharp — Sat, 22 Oct 2016 03:11:21 GMT

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)

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

Aman4SAS — Sat, 22 Oct 2016 04:55:23 GMT

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.

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

Patrick — Sat, 22 Oct 2016 05:35:43 GMT

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

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

Ksharp — Sat, 22 Oct 2016 05:48:21 GMT

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;

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

Aman4SAS — Sat, 22 Oct 2016 10:01:26 GMT

Thanks a lot.

It works.

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

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

Damien_Mather — Mon, 24 Oct 2016 03:45:41 GMT

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

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

Reeza — Mon, 24 Oct 2016 03:56:10 GMT

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?

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

RW9 — Mon, 24 Oct 2016 08:20:27 GMT

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.

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

Damien_Mather — Mon, 31 Oct 2016 10:01:45 GMT

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.