re: Range Values

[twildone]

Hi, I am assigning a Preclus number from a dataset named Replic5 to the records in a second dataset named Summary34 where the unit price is betweeen the minimum and maximum unit prices on table Replic5. I am using the following procedure:

PROC SQL NOPRINT;

CREATE TABLE SUMMARY34A AS

  SELECT

   SUMMARY34.*,

   (SELECT REPLIC5.PRECLUS FROM WORK.REPLIC5

   WHERE SUMMARY34.UNIT_PRICE BETWEEN REPLIC5.MINIMUM_UNIT_PRICE AND REPLIC5.MAXIMUM_UNIT_PRICE) AS PRECLUS

  FROM WORK.SUMMARY34, WORK.REPLIC5;

QUIT;

It seems to work fine but unfortunately there are some unit prices that fall outside any of the ranges. Is there a way that when the unit price is Not within the minimum and maximum unit prices, that the Preclus number that is selected where the unit price is the closest to either the minimum unit price or manximum unit price. Thanks

PRECLUS	Minimum_Unit_Price	Maximum_Unit_Price
1	1202.58250	1642.20000
2	4672.48100	4925.19000
3	2570.91600	2998.60000
5	190.29926	591.42600
6	663.30000	1029.10500
7	9999.99000	9999.99000
8	68.55184	105.45990
9	6.20950	20.00000
10	40.65528	65.55000
11	171.38000	181.50000
12	20.57150	39.81725
13	113.59660	139.62965
14	0.00670	6.13310
15	153.03160	168.72628

[RW9]

Probably best to do it in a join rather than a subquery:

PROC SQL NOPRINT;

CREATE TABLE SUMMARY34A AS

  SELECT

   SUMMARY34.*,

     case     when REPLIC5.PRECLUS is null then DEFAULT.PRECLUS

                else REPLIC5.PRECLUS end as ...

  FROM WORK.SUMMARY34, WORK.REPLIC5

left join

WORK.REPLIC5

   on SUMMARY34.UNIT_PRICE BETWEEN REPLIC5.MINIMUM_UNIT_PRICE AND REPLIC5.MAXIMUM_UNIT_PRICE

left join

(select min (UNIT_PRICE) from

WORK.REPLIC5) DEFAULT

   on ...

;

QUIT;

Something like that.  Sorry, computer is just about to shutdown so can't be more specific right at this moment.

[twildone]

Hi RW9,

Thanks for your help.....I was able to resolve the problem by modifying the Replic5 dataset fby adjusting the minimum and maximum unit prices first before the sub query. I t works perfectly.