BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Ksharp
Super User
hi, you just need a format.
Such as:

[pre]
proc format;
value $fmt
'A','B','C' = 'D';
run;
..................
................
format id $fmt.;
[/pre]
Reeza
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 783 views
  • 0 likes
  • 4 in conversation