Help!

[Ianacct]

Hi

Anybody can give me some help? how to code my study?

Fdate refer to forecast announcement date, Adate refers to actual number announcement date, F_value refers to forecast value, A_value refers to actual value, and Analysts code identify specific estimator. I need to create two variables (say v1 and v2).

First, I need to find the estimator whose Fdate is the closest to Adate ( in this example, it is should be analysts #9). v1=A_value-F_value.(3.15-3.5) Then, same analysts, I need to find his/her earliest Fdate ( in this example, it is should be 20011228). v2=A_value-F_value.(3.15-3.1)

Anybody can give me some direction how to code my study?

Fdate	Adate	F_value	A_value	Analysts code
20010305	20021002	3	3.15	5
20011228	20021002	3.1	3.15	9
20020223	20021002	3.2	3.15	7
20020518	20021002	3.3	3.15	5
20020826	20021002	3.4	3.15	7
20021001	20021002	3.5	3.15	9

[kannand]

Hello,

This should get you what you are looking for.... The 2 vars with prefix "New_" should get the right values for the Analyst_Code .... The Red and Blue are the 2 subqueries that will feed the data to the wrapper query. If you run each query separately and view the results, you will easily understand what is going on in this select structure. It is really not as bad as it looks. Once you indent the code, it will be pretty clean and easy to read. 

Good Luck ...!!!  Hope this helps you.....

data have;infile datalines dlm='09'x;
input
F_Date:yymmdd8.
A_Date:yymmdd8.
F_Value
A_Value Analyst_Code;
datalines;
20010305 20021002 3 3.15 5
20011228 20021002 3.1 3.15 9
20020223 20021002 3.2 3.15 7
20020518 20021002 3.3 3.15 5
20020826 20021002 3.4 3.15 7
20021001 20021002 3.5 3.15 9
;run; 

proc sql;

SELECT Y.MIN_F_DATE AS NEW_F_DATE FORMAT=MMDDYY10., Y.F_VALUE AS NEW_F_VALUE,Z.F_DATE FORMAT=MMDDYY10.,Z.A_DATE FORMAT=YYMMDD10., Z.F_VALUE, Z.A_VALUE,
Z.ANALYST_CODE
FROM
(select b.analyst_code, b.min_f_date, b.f_value, b.a_value,b.min_f_date from
(select distinct analyst_code , min(f_date) as min_f_date format=mmddyy10. from have group by analyst_code) a,
(SELECT DISTINCT ANALYST_CODE,a_value,F_VALUE ,a_value, MIN(F_DATE) AS MIN_F_DATE FORMAT=MMDDYY10. FROM HAVE GROUP BY ANALYST_CODE,a_value,f_VALUE) b
where b.analyst_code = a.analyst_code
and b.min_f_date = a.min_f_date ) Y,
(select F_DATE FORMAT =YYMMDD10., A_DATE FORMAT =YYMMDD10., F_VALUE, A_VALUE, ANALYST_CODE, THE_DATE, MIN_DATE
from (select *, (a_date-f_date) as the_date from have) A,
(select MIN (a_date-f_date) as MIN_date from have ) B
WHERE A.THE_DATE = MIN_DATE) Z
WHERE Y.ANALYST_CODE = Z.ANALYST_CODE;

 quit;

[ChrisNZ]

Like this?

data HAVE (index=(DIF_DATE A=(ANALYST_CODE F_DATE)));
  input F_DATE yymmdd9.
        A_DATE yymmdd9.
        F_VALUE
        A_VALUE 
        ANALYST_CODE;
  DIF_DATE  = abs(A_DATE-F_DATE);
  DIF_VALUE = A_VALUE-F_VALUE;
cards;
20010305 20021002 3   3.15 5
20011228 20021002 3.1 3.15 9
20020223 20021002 3.2 3.15 7
20020518 20021002 3.3 3.15 5
20020826 20021002 3.4 3.15 7
20021001 20021002 3.5 3.15 9
run; 

data WANT;
  set HAVE;
  by DIF_DATE;   
  V1=DIF_VALUE;
  set HAVE key=ANALYST_CODE;
  V2=DIF_VALUE;  
  output;
  stop;
run;