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?
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.