Help using Base SAS procedures

re: Range Values

Reply
Regular Contributor
Posts: 229

re: Range Values

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

PRECLUSMinimum_Unit_PriceMaximum_Unit_Price
11202.582501642.20000
24672.481004925.19000
32570.916002998.60000
5190.29926591.42600
6663.300001029.10500
79999.990009999.99000
868.55184105.45990
96.2095020.00000
1040.6552865.55000
11171.38000181.50000
1220.5715039.81725
13113.59660139.62965
140.006706.13310
15153.03160168.72628
Super User
Super User
Posts: 7,996

Re: re: Range Values

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.

Regular Contributor
Posts: 229

Re: re: Range Values

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.

Ask a Question
Discussion stats
  • 2 replies
  • 236 views
  • 3 likes
  • 2 in conversation