Create an SCD dataset with Base SAS code

[cxkev]

Hi,

I have a dataset that contains a record for each account number for each day and what queue the account currently sits in.

Sample Data:

AccNum          Queue          ExtractDate

A001               1                    27APR2015

A002               1                    27APR2015

A003               2                    27APR2015

A001               1                    28APR2015

A002               1                    28APR2015

A003               2                    28APR2015

A001               3                    29APR2015

A002               3                    29APR2015

A003               4                    29APR2015

If possible, I would like to cerate an SCD dataset that would reduce the size of this dataset and would not take as long to query as it would only capture changes and not store an observation for every account every day, I do not have DI studio. see below:

AccNum          Queue          RowStartDate          RowEndDate

A001               1                    27APR2015          28APR2015

A002               1                    27APR2015          28APR2015

A003               2                    27APR2015          28APR2015          

A001               3                    29APR2015          31DEC9999

A002               3                    29APR2015          31DEC9999

A003               4                    29APR2015          31DEC9999

31dec999 indicates current record.

Once the initial dataset is created, I will continue to receive extracts as in the first piece of sample data above and I will need to use them to update the SCD dataset.

Many thanks for any help.

[Ksharp · Accepted solution]

You could refer to the paper

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

Me ,Matt and Arthur.T written at this year SGF2015 ,it is about MERGE skill to transpose data.

data have;
input AccNum  $        Queue          ExtractDate : date9.;
cards;
A001               1                    27APR2015
A002               1                    27APR2015
A003               2                    27APR2015
A001               1                    28APR2015
A002               1                    28APR2015
A003               2                    28APR2015
A001               3                    29APR2015
A002               3                    29APR2015
A003               4                    29APR2015
;
run;
data have;
 set have;
 length name $ 20;
 if AccNum='A001' then n+1;
 if mod(n,2)=1 then name='RowStartDate';
  else name='RowEndDate';
drop n;
run;

proc sql;
 select distinct catt('have(where=(name="',name,'") rename=(ExtractDate=',name,'))') into : list separated by ' '
  from have;
quit;

data want;
 merge &list ;
 if missing(RowEndDate) then RowEndDate='31DEC9999'd;
 format RowStartDate  RowEndDate date9.;
 drop name;
run; 

Xia Keshan

[cxkev]

Thanks for that Xia, that is really helpful. It works on the sample data, but should it work when adding more data?

For example, I tried the following sample data (adding data for 26APR2015, where accounts were in the same queues):

data have;

input AccNum  $        Queue          ExtractDate : date9.;

cards;

A001               1                    26APR2015

A002               1                    26APR2015

A003               2                    26APR2015

A001               1                    27APR2015

A002               1                    27APR2015

A003               2                    27APR2015

A001               1                    28APR2015

A002               1                    28APR2015

A003               2                    28APR2015

A001               3                    29APR2015

A002               3                    29APR2015

A003               4                    29APR2015

;

run;

But it didn't quite yield the expected results, For example, I would want to see 2 observations for A001, one with the row start date 26APR2015 & Row end date 28APR2015 in queue 1 then the second observation for A001 to show row start date 29APR2015 and row end date 31DEC9999.

Thanks again for your help

[LinusH]

First, you don't know how to build your SCD type 2 logic. You don't have DI Studio.

My guess is that you wish ti implement this logic for more than one table. And perhaps make it dynamic so you could reuse your code.

Now it's getting a bit complex. And any implemented code needs maintenance.

Bottom line is, there's reason for people to use ETL tools. By the time and money spent at each any any site by developing they own logic, I'm quite certain that an investment in a proven tool will be more cost effective in the long run.

[cxkev]

Hi,

Only one table but I take your point. I'll close off the discussion.

Thanks for your help.

Regards,

[Ksharp]

Then what is your output ?

[santosh_pat69]

Hi all,
Even I'm trying to achieve the same using Base SAS programmer Ksharp I have tried using your code but I'm getting missing values for the start dates.
My Sample data has around 14800 records,a free the first run the count was 7300 which is fine.
Kindly Suggest