topic data selection help in SAS Procedures

data selection help

raveena — Wed, 27 Jul 2011 17:31:17 GMT

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.

data selection help

Doc_Duke — Wed, 27 Jul 2011 18:01:32 GMT

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

Re: data selection help

Howles — Wed, 27 Jul 2011 21:13:23 GMT

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

Re: data selection help

raveena — Wed, 27 Jul 2011 21:23:13 GMT

Thanks Howles for your help..