Merge

[jen123]

I have two tables.  Table 1 is a list of IDs (eval_rl_id).   Table 2 is a list of queues and the rules that work in those queues. I want to combine tables 1 and 2 to get table 3.  I tried to join them in this order:

eval_rl_id left join rule_id1

eval_rl_id left join rule_id2

eval_rl_id left join rule_id3

eval_rl_id left join rule_id4

eval_rl_id left join rule_id5

The results were many-many, which is incorrect.

I tried merge : that too did not work for me.

Proc Sort data=book2;

by eval_rl_id;

run;

Proc Sort data=queue_rsn_rule_id_mapping;

by rule_id1;

run;

data rule1;

merge book2 (rename=(eval_rl_id=rule_id1)) queue_rsn_rule_id_mapping;

  by rule_id1;

  run;

************************************

table 3

EVAL_PRITY_CD	EVAL_RL_ID	eval_description	Total Applications	queue1	queue2	queue3	queue4	queue5
2100	4703	Description1	31	123	456	789
1152	4709	Description2	22	789
3600	5102	Description3	20	123	789

table1

EVAL_PRITY_CD	EVAL_RL_ID	eval_description	Total Applications
2100	4703	Description1	31
1152	4709	Description2	22
3600	5102	Description3	20

table 2

queue	dcsn_by	priority_cd	rule_id1	rule_id2	rule_id3	rule_id4	rule_id5	Total
123	emp1	2150	4703	15005	5102	(blank)	(blank)	4
456	emp2	19000	2876	4703	(blank)	(blank)	(blank)	1
789	emp1	19000	4709	4703	5102	(blank)	(blank)	3

etc...

[sunilzood]

Dear Jen, Looking your output in table 3 seems that table2 is not created well. It tried it with a formateed way & results is good. Hope it helps if i understood your request well.

All d best..

data table1;
input EVAL_PRITY_CD EVAL_RL_ID eval_description :$15. Total_Applications  ;
cards;

2100 4703 Description1 31
1152 4709 Description2 22
3600 5102 Description3 20
;
run;

data table2;
input queue dcsn_by $ priority_cd rule_id1 rule_id2 rule_id3 rule_id4 rule_id5 Total ;
cards;
123 emp1 2150 4703 15005 5102 . . 4
456 emp2 19000 2876 4703 . . . 1
789 emp1 19000 4709 4703 5102 . . 3
; run;

proc sort data = table1; by EVAL_RL_ID; run;
proc sort data = table2; by rule_id1; run;

data merge_both;
merge table1 (in = aa rename = (EVAL_RL_ID = rule_id1)) table2 ( in = bb);
by rule_id1;
if aa = 1;
run;

sunil

[Astounding]

Jen,

The major part of the work will be reshaping Table 2 into a form that will merge easily with Table 1.  Here's one way to go about it:

data table2_reformatted;

   set table2;

   array rules {5} rule_id1 - rule_id5;

   do _n_=1 to 5;

      if rules{_n_} > . then do;

         eval_rl_id = rules{_n_};

         output;

      end;

   end;

   keep eval_rl_id queue;

run;

Your post doesn't specify whether the variables are character or numeric, so I'm treating them as numeric here.  The reformatting isn't finished yet ...

proc sort data=table2_reformatted;

   by eval_rl_id;

run;

proc transpose data=table2_reformatted prefix=queue out=table2_final (keep=eval_rl_id queue:);

   by eval_rl_id;

   var queue;

run;

That takes care of reshaping the data and preparing for the final step:

data want;

   merge table1 table2_final;

   by eval_rl_id;

run;

This is untested code, so you may need to tweak it.  But this should put you on a working path.  Take the time to examine the results along the way to get a sense of how the reshaping takes place.

Good luck.