Day difference

[SASPhile]

initial has two varibles acct no and date

3713  20140323

3713  20140325

expand has two variables acct no and date

3713  20140324

final has two varibles acct no and date

3713  20140324

to create a dataset All with following variables:

acctno  date_initial  day0           day1_e day2_e day3_e day4_e day5_e  day1_f day2_f day3_f day4_f day5_f

3713     20140323      1                      1      0      0      0       0      1     0      0      0       0   
3713     20140325      1                      0      0      0      0       0      0     0      0      0       0

for the first record:
  day0 is set to default 1
  day1_e is if the date in expand dataset is greather than date in initial for the same acct no, and if the difference between the dates is 1 then day1_e is set to 1 and rest all day2_e to day5_e are set to 0 (day1_e to day5_e are the difference in days between  expand and initial dates, if the difference between them is  say 3
   day3_e will be set to 1 and rest all to 0.maximum allowed is 5 days)

the same applies to day1_f to day5_f.

for the second row,since date in initial is greater than date in expand and final datasets, we will have 0's in all day columns except day0.

How to do the above?      

[Reeza]

1. Merge the files together so that you have the following on the same row: acctno date_initial date_expand date_final.

2. In a data step, create two sets of arrays day1_e to day5_e and same for final, initialize to zero values.

3. Calculate the difference between dates from step #1 as and increment to 1

diff1=date_expand-date_initial;

if diff1>0 then date_array_e(diff1)=1;

diff2 = date_final-date_initial;

if diff2>0 then date_array_f(diff2)=1;

If diff is less than zero can exit the step.

[Ksharp]

As Reeza said. merge it all together , then judge it.

data initial;
input acctno date_initial :yymmdd10.;
format date_initial yymmdd10.;
cards;
3713  20140323
3713  20140325
;
run;
data expand;
input acctno date_initial :yymmdd10.;
format date_initial yymmdd10.;
cards;
3713  20140324
;
run;
data final;
input acctno date_initial :yymmdd10.;
format date_initial yymmdd10.;
cards;
3713  20140324
;
run;

data w;
 merge initial expand(rename=(date_initial=e_date)) final(rename=(date_initial=f_date));
 by acctno;
 retain day0  1;
 array _e{*} day1_e day2_e day3_e day4_e day5_e;
 array _f{*} day1_f day2_f day3_f day4_f day5_f;
 dif_e=e_date-date_initial;
 dif_f=f_date-date_initial;
 do i=1 to dim(_e);
  if i=dif_e then _e{i}=1;else _e{i}=0;
  if i=dif_f then _f{i}=1;else _f{i}=0;
 end;
drop i e_date f_date dif_e dif_f;
run;

Xia Keshan