How to select within the closest thing to Y in SAS as a starting point

Question

So I have a dataset where I want to select the closest records to point X for my output,

What I have is

PROC SQL ;
   create table Check_vs_Excel2 as
    SELECT PROPERTY, START_DATE, END_DATE, DAY_OF_WEEK, MARKET_CODE_PREFIX, RATE_PGM, ROOM_POOL, QUOTE_SERIES_NO, QUOTE_POSITION
    FROM Sbtddraf.Vssmauditdraftfull
      group by Property, RATE_PGM
    having START_DATE = MAX(START_DATE);

quit;

I want to take the START_DATE = Max(Start_DATE); and change it to something which is (effectively)

having START_DATE = close to(TODAY())

Advice would be much appreciated

score 1 · Accepted Answer · answered Mar 16 '16 at 16:59

In SQL your query would be using a Correlated Subquery:

SELECT PROPERTY, START_DATE, END_DATE, DAY_OF_WEEK, MARKET_CODE_PREFIX, RATE_PGM, ROOM_POOL, QUOTE_SERIES_NO, QUOTE_POSITION
FROM Sbtddraf.Vssmauditdraftfull AS t
--   group by Property, RATE_PGM
WHERE START_DATE =
  ( select MAX(START_DATE)
    FROM Sbtddraf.Vssmauditdraftfull AS t2
    where t1.Property = t2.Property
      and t1.RATE_PGM = t2.RATE_PGM
  )

score 0 · Answer 2 · answered Mar 16 '16 at 16:21

0

how close is 'close to'? This might be what you are looking for

      where START_DATE between TODAY() and DATE_ADD(TODAY(),INTERVAL 30 DAY)

answered Mar 16 '16 at 16:21

Seda

243
1
9

I tried this and fiddled with it a little but I can't do date_add in proc sql. Do you know how I might be able to use INTNX in something similar? – Kumachorou Mar 16 '16 at 16:51
I would try something along these lines but im not 100% sure START_DATE between TODAY() and INTNX('day',TODAY(),30) – Seda Mar 16 '16 at 17:04

score 0 · Answer 3 · answered Mar 16 '16 at 17:58

Assuming I understand that you want the row that has the minimum absolute difference between start_date and today() (so, MIN(ABS(START_DATE-TODAY()))), you can do a somewhat messy query using the having clause this way:

data have;
  do id = 2 to 9;
    do start_date = '02MAR2016'd to '31MAR2016'd by id;
      output;
    end;
  end;
run;

proc sql;
  select id, start_date format=date9.
    from have
    group by id
    having abs(start_date-today()) = min(abs(start_date-today()));
quit;

I don't like this in part because it's non-standard SQL and gives a note about re-merging data (it's non-standard and gives you that note because you're using a value that's not really available in a group by), and in part because it gives you multiple rows if two are tied (see id=4 if you run this on 3/16/2016).

A correlated subquery version, which at least avoids the remerging note (but actually does effectively the same thing):

proc sql;
  select id, start_date format=date9.
    from have H
    where abs(start_date-today()) = (
      select min(abs(start_date-today()))
      from have V
      where H.id=V.id
      );
quit;

Still gives two for id=4 though (on 3/16/2016). You'd have to make a way to pick if there are possibly two answers (or perhaps you want strictly less than?). This does a subquery to determine what the smallest difference is then returns it.

`V` and `H` are table aliases - just things that let you write a shorter statement, like `H.id = V.id`. In addition, since `have` is used twice here, you _have_ to use a table alias - otherwise there's no way to identify which `have` is which. — Joe, Mar 17 '16 at 14:47

How to select within the closest thing to Y in SAS as a starting point

3 Answers3