DATA Step, Macro, Functions and more

RE:PROC SQL

Accepted Solution Solved
Reply
Regular Contributor
Posts: 229
Accepted Solution

RE:PROC SQL

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


Accepted Solutions
Solution
‎03-05-2015 10:13 PM
Respected Advisor
Posts: 4,923

Re: RE:PROC SQL

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

PG

View solution in original post


All Replies
Solution
‎03-05-2015 10:13 PM
Respected Advisor
Posts: 4,923

Re: RE:PROC SQL

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

PG
Regular Contributor
Posts: 229

Re: RE:PROC SQL

Thanks PG....It did work.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 244 views
  • 1 like
  • 2 in conversation