@siope1kd
The following is a bit more involved but it would allow you to apply the new "index" on-the-fly without a need to update your table.
The code uses a format on column "shipment_reindex". The specialty is that the format itself encapsulates the function which does the actual calculation. The only thing you would need to do on a monthly basis is to update the function instead of all the values in your have table.
Actually: You wouldn't even need an extra column "shipment_reindex" but you just apply the format to column "shipment_date" whenever you need it.
Sample code below:
/* data after appending latest month */
data have;
format shipment_date date9.;
do shipment_date="01Apr2015"d to "31Mar2017"d;
Shipment_Month=month(shipment_date);
Shipment_reindex=shipment_date;
output;
end;
run;
/* determine latest shipment_date in table */
proc sql noprint;
select max(shipment_date) into :max_shipment_date
from have
;
quit;
/* recreate function using &max_shipment_date */
proc fcmp outlib=sasuser.funcs.myfuncs;
function shipment_reindex(in_date) ;
month_index = 12-mod(intck('month',in_date,&max_shipment_date),12);
return (month_index);
endsub;
run;
options cmplib=(sasuser.funcs);
proc format lib=sasuser;
value _shipment_reindex
other=[shipment_reindex()];
run;
/* apply format to dataset have */
proc datasets lib=work;
modify have;
format Shipment_reindex _shipment_reindex2.
;
run;
quit;
... View more
