data selection help

[raveena]

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

9087 don 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

9087 don 06/29/2011

Please let me know how to apply this logic by using proc sql.

Thanks in advance.

[Doc_Duke]

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

[Howles]

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

[raveena]

Thanks Howles for your help..