Retroactive Adjustments to a dataset

[saspert]

Hi,

I have had similar issues while working at my previous clients. Just wondering if there is an efficient way to manage these datasets -

Month1

Mbr1 Prod1 Amt1

Mbr1 Prod2 Amt 2

Mbr2 Prod1 Amt1

Now in Healthcare companies, due to membership changes during a year, members drop off and join back. The issue comes while using proc sql to update the dataset. How do we update a member's record to show the following information the next month.

Month2

Mbr1 Prod1 Amt1 Adjamt1

Mbr1 Prod2 Amt1              Amt3

Mbr2 Prod1 Amt1 Adjamt2

The final report usually sums up the amt1 -3 +/- adjamt1-3 and gives 1 number to a member product. But maintaining the datasets on a monthly basis becomes a bit complex.

Thanks,

saspert.