BookmarkSubscribeRSS Feed
twildone
Pyrite | Level 9

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
2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 726 views
  • 3 likes
  • 2 in conversation