topic Re: DI Studio - Holding Value Across Rows in SAS Data Management

DI Studio - Holding Value Across Rows

jwhite — Tue, 11 Oct 2016 17:26:08 GMT

I need to do some clean-up on a few dim tables in our warehouse. It looks like our two columns that have the 'valid from date' and 'valide to date', which allow us to find the current record in a set of historical records, were not loaded correctly some time ago.

For example, we may have a record like follows:

ID Valid from Valid to

1 01JUN2007 01JAN5999

1 01JAN2004 01JAN5999

4 01JUN2012 01JAN5999

4 16AUG2014 01JAN5999

4 01JAN2010 30JUL2012

4 01DEC2013 0!JAN5999

Clearly in each of these cases, only one record should have a 'Valid To' value of 01JAN5999....know we've got to fix it. I hope I made it clear in the example that it varies the number of records an ID may have.

I was thinking if I could sort the the columns and retain the 'Valid From' record of the previous row, subtract 1 from the date, then I could have the correct 'Valid To' date.

Can this be done? Am I on the right path, or is there another approach?

Thanks!

Re: DI Studio - Holding Value Across Rows

LinusH — Tue, 11 Oct 2016 19:55:06 GMT

I think it sounds like a feasible approach.

Unless your data is huge. Then you might want o filter out the rows with high date, and possible whole id groups, fix the necessary records separately, and do a table update/modify by.

Just be aware that processing your data this way, indexes & constraints will vanish, so you need to apply them to your fixed data.

Re: DI Studio - Holding Value Across Rows

jwhite — Tue, 11 Oct 2016 20:58:48 GMT

Thanks, but how do I retain the value from one row to then do a -1 day expression on the next to get the new close date?

Re: DI Studio - Holding Value Across Rows

Patrick — Wed, 12 Oct 2016 01:54:57 GMT

Assuming the valid from dates are correct and you don't have the same value more than once per ID then below code gives you the logic for re-alignment.

data have;
  infile datalines truncover;
  input id (Valid_from_dttm Valid_to_dttm) (:date9.);
  format Valid_from_dttm Valid_to_dttm date9.;
  datalines;
1 01JUN2007 01JAN5999
1 01JAN2004 01JAN5999
4 01JUN2012 01JAN5999
4 16AUG2014 01JAN5999
4 01JAN2010 30JUL2012
4 01DEC2013 01JAN5999
;
run;

proc sort data=have out=have_sorted;
  by id Valid_from_dttm;
run;

data updates;
  set have_sorted;
  by id;
  format Valid_to_dttm_REALIGNED date9.;
  if not last.id then
    do;
      _ind=_n_+1;
      set want 
        ( 
        keep=Valid_from_dttm 
        rename=(Valid_from_dttm=_Valid_from_dttm)
        )
        point=_ind
        ;

      if Valid_to_dttm>_Valid_from_dttm then 
        do;
          Valid_to_dttm_REALIGNED=_Valid_from_dttm-1;
          output;
        end;
    end;
run;

If your data is stored in a data base and you'r dealing with large data volumes then I would implement the logic directly in-database (eg. using analytics functions).

What you can't re-construct are cases where a record had been expired but then a month later got re-activated (if that's possible with your data). So something like below couldn't get reconstructed:

1 01JUN2004 01DEC2006
1 01JAN2007 01JAN5999

Re: DI Studio - Holding Value Across Rows

LinusH — Wed, 12 Oct 2016 08:19:25 GMT

Resort your data by Id descending valid_from_dt.
Use retain for bot from ant to dt.
And then assign a new to dt with "previous" from dt-1 when appropriate.
Another crazy idea is the set all high dates to missing and then process the data through the SCD Type 2 Loader.

Re: DI Studio - Holding Value Across Rows

jwhite — Fri, 14 Oct 2016 14:07:43 GMT

Thanks @Patrick and @LinusH!

Actually blanking out all the values in that value_to_date column, then running through the SCD Type 2 loader did the trick.

Cheers!

Re: DI Studio - Holding Value Across Rows

Patrick — Fri, 14 Oct 2016 14:12:14 GMT

Sounds like a "hack". So using this approach you didn't create new records? And it really re-aligned all values also historical ones?

Re: DI Studio - Holding Value Across Rows

jwhite — Fri, 14 Oct 2016 14:19:14 GMT

It did not create any new records, but did realign the open and close dates for a given ID.

We also had a few cases where there would only be a singular record (ID) but was closed. This should be open so that each ID has an open record. It opened those cases with the '01JAN5999' date.

I feared it may create new records as well, but was pleased to see it didn't.