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

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Lapis Lazuli | Level 10
the sql monotone() function is supported. select .,.,., monotone(date) as sequence, .,.,. would create an _n_ - like field.
Reeza
Super User

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
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

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
Obsidian | Level 7

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
Opal | Level 21

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
Super User

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
Obsidian | Level 7

Thanks a lot. 

 

It works.

 

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 11 replies
  • 1403 views
  • 0 likes
  • 6 in conversation