Please help me with asigning an index number to each date

[Anna_Guo]

I want to asign an index number for each date. But some dates are missing in the index table, in this case I will asign index number of the nearest next date. I attached a file which has two tables.

I know proc sql can add index number, but my problem is that some dates are not in  one table.

Thanks a lot.

[art297 · Accepted solution]

One way is to create and apply a format.  e.g.:

data Table1;

  informat IndexDate anydtdte.;

  format IndexDate date9.;

  input IndexDate datenumber;

  cards;

01-01-1997 1

01-02-1997 2

01-03-1997 3

01-06-1997 4

01-07-1997 5

01-08-1997 6

01-09-1997 7

01-10-1997 8

1/13/1997 9

1/14/1997 10

1/15/1997 11

1/16/1997 12

1/17/1997 13

1/20/1997 14

1/21/1997 15

1/22/1997 16

1/23/1997 17

1/24/1997 18

1/27/1997 19

1/28/1997 20

1/29/1997 21

1/30/1997 22

1/31/1997 23

02-03-1997 24

;

data table1_expanded (drop=_:);

  set table1 (rename=(IndexDate=start datenumber=label));

  retain fmtname "dates" type "N" ;

  _date=lag(start);

  if not missing(_Date) and start gt _date+1 then do;

    _hold_IndexDate=start;

    do start=_date+1 to _hold_IndexDate;

      output;

    end;

  end;

  else output;

run;

proc format cntlin = table1_expanded ;

run ;

data Table2;

  informat D1-D3 anydtdte.;

  format D1-D3 date9.;

  input D1-D3;

  cards;

01-04-1997 1/21/1997 1/23/1997

01-05-1997 1/17/1997 1/24/1997

1/17/1997 1/20/1997 1/27/1997

1/20/1997 1/21/1997 01-09-1997

1/21/1997 1/25/1997 1/17/1997

1/22/1997 02-01-1997 1/20/1997

1/23/1997 1/21/1997 1/21/1997

1/24/1997 1/22/1997 1/22/1997

;

data want;

  set table2;

  d1_num=put(d1,dates.);

  d2_num=put(d2,dates.);

  d3_num=put(d3,dates.);

run;

[Anna_Guo]

Arthur,

Thank you very much.