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.
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;
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;
Arthur,
Thank you very much.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.