Hi,
Currently am generating the report for the processing month (06/01/2011- 07/01/2011)
and need to select the greatest pidate data from the processing month.
For example,
ID NAME PIDATE
1234 david 04/22/2011
3456 roy 07/10/2011
4567 yak 06/15/2011
8976 ron 06/29/2011
From above i need to select a PIDATE within the processing month (06/01/2011- 07/01/2011)
and the greatest pidate value (i.e 06/29/2011)
Output should be,
8976 ron 06/29/2011
Please let me know how to apply this logic by using proc sql.
Thanks in advance.
Something like this should work (untested code):
SELECT * FROM <data>
WHERE pidate BETWEEN '01jun2011'd AND '30Jun2011'd
AND pidate=(SELECT max(pidate) FROM <data> WHERE pidate BETWEEN '01jun2011'd AND '30Jun2011'd );
A couple of notes:
-- the BETWEEN operator is inclusive, so I modified your dates.
-- this is a correlated subquery, so performance can be poor in large datasets (see documentation).
Doc Muhlbaier
Duke
Avoid the correlated subquery
select *
from demo
where pidate BETWEEN '01jun2011'd AND '30Jun2011'd
having pidate EQ max(pidate);
This does employ remerging.
Doc@Duke wrote:
Something like this should work (untested code):
SELECT * FROM <data>
WHERE pidate BETWEEN '01jun2011'd AND '30Jun2011'd
AND pidate=(SELECT max(pidate) FROM <data> WHERE pidate BETWEEN '01jun2011'd AND '30Jun2011'd );
A couple of notes:
-- the BETWEEN operator is inclusive, so I modified your dates.
-- this is a correlated subquery, so performance can be poor in large datasets (see documentation).
Doc Muhlbaier
Duke
Thanks Howles for your help..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.