topic Re: Create an SCD dataset with Base SAS code in SAS Programming

Create an SCD dataset with Base SAS code

cxkev — Wed, 06 May 2015 10:46:20 GMT

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.

Re: Create an SCD dataset with Base SAS code

Ksharp — Wed, 06 May 2015 12:25:33 GMT

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

Re: Create an SCD dataset with Base SAS code

cxkev — Mon, 11 May 2015 10:40:04 GMT

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

Re: Create an SCD dataset with Base SAS code

LinusH — Mon, 11 May 2015 10:44:49 GMT

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.

Re: Create an SCD dataset with Base SAS code

cxkev — Mon, 11 May 2015 11:46:42 GMT

Hi,

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

Thanks for your help.

Regards,

Re: Create an SCD dataset with Base SAS code

Ksharp — Mon, 11 May 2015 11:53:14 GMT

Then what is your output ?

Re: Create an SCD dataset with Base SAS code

santosh_pat69 — Wed, 17 Aug 2016 11:13:19 GMT

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