Hello,
Imagine that you bought an auto insurance policy (duration = 12 months), let's say in January 2023 having number x123456. This policy will have transactions (observations) in Jan, Fev, Mar, Apr, May, Jun, Jul, Aug, Sep, oct, nov, dec 2023 and Jan 2024 and maybe in Fev 2024.
Therefore those transactions (observations) related to that policy will be spead into 12 or 13 datasets such as:
&co._auto_prmjan2023, &co._auto_prmfeb2023, ..., &co._auto_prmdec2023, &co._auto_prmjan2024, and maybe &co._auto_prmfeb2024.
Now imagine that I need to remove those transactions (observations) from the original datasets
&co._auto_prmjan2023, &co._auto_prmfeb2023, ..., &co._auto_prmdec2023, &co._auto_prmjan2024, and maybe &co._auto_prmfeb2024 and to put those into new datasets like:
&co._auto_prmjan2023_dr, &co._auto_prmfeb2023_dr, ..., &co._auto_prmdec2023_dr, &co._auto_prmjan2024_dr, and maybe &co._auto_prmfeb2024_dr.
(co = company abreviation)
For only one policy, this task seems simple.
But the problem, I have a 1 200 000 policies spread into approximatively 7128 datasets. I have produced a SAS code able to read the policies list from a csv file,
then to scan at these 7128 datasets and tell me for each policy into that list, in which dataset I can find transactions related to each policy.
So now, I have a 12 000 000 observations datset, giving me the agreement_nbr, the path, the filename where each policy host transactions.
I could do a call execute to remove the observations related to each policy, one by one and to put these into datasets with the same name but with the extension ...._dr ex:
move transaction for policy x123456 from
dataset be._auto_prmjan2023 to dataset be_auto_prmjan2023_dr
dataset be._auto_prmfeb2023 to dataset be_auto_prmfeb2023_dr
...
dataset be._auto_prmfeb2024 to dataset be_auto_prmfeb2024_dr
and that only for the policy x123456
But from my 12 000 000 observations dataset, I know which policies need to be move from dataset be._auto_prmjan2023 to be._auto_prmjan2023_dt, from dataset be._auto_prmfeb2023 to be._auto_prmfeb2023 and so on.
What's the more efficient way to move those transactions, one by one, or to group those per dataset and move those in one shot.
And how to do that? Please provide an example (SAS snippets).
Ex: dataset be._auto_prmjan2023
x123456
x123457
x123458
x123459
need to move x123456 and 123459
After removing those policies transactions
be._auto_prmjan2023
x123457
x123458
dataset be._auto_prmjan2023_dr
x123456
x123459
I need to do this task but for approx. 1 000 000 policies
I hope you understand what I need to do.
So how having my data 12 000 000 dataset, how can I do those transactions shifts by policies group ?
... View more