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
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
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
Thanks PG....It did work.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.