Help using Base SAS procedures

Please help me with asigning an index number to each date

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Please help me with asigning an index number to each date

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.

Attachment

Accepted Solutions
Solution
‎01-09-2013 01:45 PM
PROC Star
Posts: 7,485

Re: Please help me with asigning an index number to each date

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=_Smiley Happy;

  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;

View solution in original post


All Replies
Solution
‎01-09-2013 01:45 PM
PROC Star
Posts: 7,485

Re: Please help me with asigning an index number to each date

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=_Smiley Happy;

  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;

Contributor
Posts: 36

Re: Please help me with asigning an index number to each date

Arthur,

Thank you very much.

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 145 views
  • 0 likes
  • 2 in conversation