RE:PROC SQL

twildone — Fri, 06 Mar 2015 01:26:38 GMT

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

Re: RE:PROC SQL

PGStats — Fri, 06 Mar 2015 03:13:33 GMT

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;

Re: RE:PROC SQL

twildone — Tue, 10 Mar 2015 14:27:48 GMT

Thanks PG....It did work.

topic Re: RE:PROC SQL in SAS Programming

RE:PROC SQL

Re: RE:PROC SQL

Re: RE:PROC SQL