topic getting the master id ladder like table in SAS Programming

getting the master id ladder like table

deleted_user — Tue, 18 May 2010 16:16:10 GMT

Hello

I have the following table with :

master_id merged_id merge_request
B________A________ 01/01/2010
C________B_________02/01/2010
D________C_________03/01/2010

where some user id where basically referencing the same person
and have merged in the base system.

How could I create a lookup table that would look like that:

master_id merged_id
D________A
D________B
D________C

The only way I can think to do this would be using a macro?

Thanks

jsg

Re: getting the master id ladder like table

Cynthia_sas — Tue, 18 May 2010 16:37:53 GMT

Hi:
I don't understand your relationships. In the "lookup" table, you have this:
[pre]
master_id merged_id
D A
D B
D C
[/pre]

How does master_id in this table correspond to master_id in the original table?
[pre]
master_id merged_id merge_request
B A 01/01/2010
C B 02/01/2010
D C 03/01/2010
[/pre]

Possible interpretations/combinations/lookups could be:
1) master_id on original table matches with master_id on lookup table and merged_id on lookup table becomes new merged_id in output table

2) merged_id on lookup table matches with master_id on original table and master_id on lookup table becomes the new master_id in output table

3) merged_id on lookup table matches with merged_id on original table and master_id on lookup table becomes new master_id in output table

There are undoubtedly more possibilities. Without any idea of how the lookup table or the original table will be used or what the merge_request date field means or what type of subsequent processing or output report you hopw to see, it is hard to make any recommendations.

To me, in the month of January, it lookes like A was merged with B. Then in Feb, it looks like B was merged with C, but does that mean that A was also merged with C, but only for Feb?? Then in March, C was merged with D, does that mean that A and B were also merged into D -- but only for March??? Your lookup table does not distinguish time periods. It looks like you are possibly rolling up all merges to the most recent master_id??? But wouldn't D be the wrong master_id for A in the month of January????

Having some idea of the whole process and how you are using these tables would come in handy.

cynthia

Re: getting the master id ladder like table

deleted_user — Wed, 19 May 2010 08:36:06 GMT

Hello

In my actual question id A,B,C and D are all the same user in the system because
A has been merged to B, then B has been set to C, etc.

In another table I have transactions for all these user id and I want to associate these transactions with the latest master id.

id transaction_type date
A JA 01/02/2010
.
.
A PU 01/02/2010
B JA 03/02/2010
.

Should become:

id transaction_type date
D JA 01/02/2010
.
.
D PU 01/02/2010
D JA 03/02/2010
.

Thanks

jsg

Re: getting the master id ladder like table

Ksharp — Thu, 20 May 2010 02:14:50 GMT

hi, you just need a format.
Such as:

[pre]
proc format;
value $fmt
'A','B','C' = 'D';
run;
..................
................
format id $fmt.;
[/pre]

Re: getting the master id ladder like table

Reeza — Thu, 20 May 2010 21:18:51 GMT

Hi JSG,

Is this a one-time run, or will the table need to be updated in the future.

Basically you're looking to create what's called a Slowly Changing Dimension.

If its a one time creation the process is different than if there will be updates.

If you have a solution already, I'd be interested in seeing it as well, if not I have a few idea's that may work.

Thanks,
Reeza