This will build you a table that I think you can use to generate whatever transactions you need to complete your list of accounts and months.
Tom
/* Get the Accounts */
proc sql noprint;
create table Check_Accounts as
select distinct Account_id as Check_Account from Sample_Data;
quit;
/* Get the earliest and latest date */
proc sql noprint;
select min(Status_Date), max(Status_Date) into :Min_Date, :Max_Date
from Sample_Data;
/* Get a list of all the needed dates */
data Check_Months(drop=_:);
_Number_Of_Months = intck('month', &Min_Date., &Max_Date.);
do _i = 0 to _Number_Of_Months;
Check_Month = intnx("month", &Min_Date., _i, 'beginning');
format Check_Month date.;
output;
end;
run;
/* Get a cross-combination of accounts and dates */
proc sql noprint;
create table Check_Table as select Check_Account, Check_Month, 'Missing' as Check_Status from Check_Accounts cross join Check_Months;
quit;
/* Get the Account and Month */
data Existing_Company_Month;
set Sample_Data;
Month_Active = intnx("month", Status_Date, 0, 'beginning');
format Month_Active date.;
keep Account_id Month_Active;
run;
/* Sort, and then keep the first (get unique) */
proc sort data=Existing_Company_Month;
by Account_id Month_Active;
run;
data Existing_Company_Month;
set Existing_Company_Month;
by Account_id Month_Active;
Current_Status = "Active";
if first.Month_Active then
output;
run;
/* Join the two tables to figure out which months we have and which ones we need to add data for */
proc sql noprint;
create table Combined_Tables as select m.*, e.Current_Status
from Check_Table m left outer join Existing_Company_Month e
on(m.Check_Account = e.Account_id and m.Check_Month = e.Month_Active)
order by m.Check_Account, m.Check_Month;
quit;
... View more
