Reduce Data by using Random Sample

_MVB_ — Thu, 25 Jun 2020 19:35:26 GMT

Hi All,

I posted similar couple weeks ago, however these days I need to perform slightly different exercise.

I attached 'have' & 'want' in the excel, please have a look.

This is just a snapshot of what I have - my data is over 10 years with over a hundred million records

What I have: several years of historical data (month) of accounts with an indicator of a late payment for a respective month and a rate (how many accounts had late payment in a given month). In each month there are unique accounts, such as Aug-10 has 1,270,000 unique accounts, while entire dataset is 33M non-unique accounts. Another point to mention, some account get closed overtime (once it happens, they won't appear on a following month), however new account added. This is why MoM total accounts go up, but not all accounts from previous month would appear on the following month.

This is why in table 'want' I would have 1,647,283 unique accounts (combination of all unique accounts that appear at least 1 time during observation period).

What I need is following:

I need to reduce data to unique account level, while not loosing insights of monthly late payment rate and overall payment rate.

Until now, I tried two approaches:

1. SurveySelect with STRATA by account and SAMPSIZE=1

2. rand('Uniform') with following sorting and NODUPKEY by account

In both cases, I could achieve reducing accounts to unique account, however when running frequency for each month and total, rates where not close to rates in 'have' - such as, oldest months came back with rate ~6.5% and earliest with ~3.5%, while total rate ~ 5.2.

Can anyone suggest procedure/steps to be taken to handle this through random selection/exclusion to reduce data and keep all insightful information?

Thanks in advance!

Re: Reduce Data by using Random Sample

ChrisNZ — Sun, 28 Jun 2020 06:39:04 GMT

Modify your post so you don't use an excel file. This might entice more replies.

topic Re: Reduce Data by using Random Sample in SAS Programming

Reduce Data by using Random Sample

Re: Reduce Data by using Random Sample