Help using Base SAS procedures

data selection help

Reply
Frequent Contributor
Posts: 90

data selection help

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.

Trusted Advisor
Posts: 2,116

data selection help

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

Regular Contributor
Posts: 184

Re: data selection help

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

Frequent Contributor
Posts: 90

Re: data selection help

Thanks Howles for your help..

Ask a Question
Discussion stats
  • 3 replies
  • 129 views
  • 0 likes
  • 3 in conversation