RE:PROC SQL

[twildone]

Hi....I am trying to join two datasets (Summary6 & Prices) by the variable 'ID' and the date from the dataset Summary6 is greater than and equal the variable 'Eff_Date' from the dataset Prices. From the code I am using below, the output I am getting is each record from the dataset Summary6 is merged with each record from the dataset Prices where the Summary.ID = Prices.ID and when Date >= Eff_Date. That is, one - to - Many. I would like the output to contain one-to-one where the ID'S are equal and the first time that the date is greater than or equal to the Eff_Date. Would SUMMARY6.ID = PRICES.ID AND SUMMARY6.DATE >= min(PRICES.EFF_DATE)  merge the two datasets and produce the correct results. Thanks in Advance

PROC SQL NOPRINT;

     CREATE TABLE SUMMARY6A AS

           SELECT

                SUMMARY6.*,

       PRICES.IDAS ID1,

       PRICES.UNIT_PRICE AS UNIT_PRICE1,

       PRICES.EFF_DATE

           FROM WORK.SUMMARY6 LEFT JOIN WORK.PRICES ON

SUMMARY6.ID = PRICES.ID AND SUMMARY6.DATE >= PRICES.EFF_DATE;

QUIT;

Sample of Prices dataset below:

ID   UNIT_PRICE EFF_DATE

1033 0.4355     20130505

1033 0.4222     20140622

1033 0.4078     20150104

1057 0.9872     20111214

1057 0.9955     20150223

1066 1.2234     20121130

1066 1.1332     20130525

1066 1.0328     20140223

1066 1.0023     20150125

[PGStats · Accepted solution]

I would do :

PROC SQL;

CREATE TABLE SUMMARYTEMP AS

SELECT

  SUMMARY6.*,

  PRICES.UNIT_PRICE AS UNIT_PRICE1,

  PRICES.EFF_DATE

FROM

  WORK.SUMMARY6 LEFT JOIN

  WORK.PRICES

  ON SUMMARY6.ID = PRICES.ID AND

  SUMMARY6.DATE >= PRICES.EFF_DATE

ORDER BY ID, DATE, EFF_DATE;

QUIT;

data SUMMARY6A;

set SUMMARYTEMP; BY ID DATE;

if first.DATE;

run;

PG

[twildone]

Thanks PG....It did work.