I'm thinking proc SQL is the best way to go about this. I have a fact table that has Id and time. The corresponding tables that work with the fact table have less time entries than the fact table. I need to join the other tables with the fact tables on Id and time then fill in blanks that come in from the other tables based on the most recent date up to that point in time. My time dimension is a character string formatted like 201010 202110 202210 formatted as year-academic period. The reason I want to use proc SQL and case statements is because I have about 10 lookup tables like the table below and its easy to use proc SQL with lookup tables.
Please advise on how to fill in the blanks.
Test tables are:
proc sql;
create table fact_example (
academic_period varchar2(6),
id int,
retained int);
insert into fact_example (academic_period, id, retained)
values ('201010', 111, 1);
insert into fact_example (academic_period, id, retained)
values ('201110', 111, 1);
insert into fact_example (academic_period, id, retained)
values ('201210', 111, 1);
insert into fact_example (academic_period, id, retained)
values ('201310', 111, 1);
create table dim_example (
academic_period varchar2(6),
id int,
variable_changes varchar(1));
insert into dim_example (academic_period, id, variable_changes)
values ('201010', 111, 'b');
insert into dim_example (academic_period, id, variable_changes)
values ('201210', 111, 'g');
Create table lookup_example (
varshort varchar(1),
varlong varchar(50));
insert into lookup_example (varshort, varlong) values ('b', 'blue');
insert into lookup_example (varshort, varlong) values ('g', 'green');
quit;
... View more
