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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.