Desktop productivity for business analysts and programmers

Merge

Reply
Frequent Contributor
Posts: 94

Merge

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_CDEVAL_RL_IDeval_description Total Applications queue1queue2queue3queue4queue5
21004703Description131 123456 789
11524709Description222 789
36005102Description320 123789

table1

EVAL_PRITY_CDEVAL_RL_IDeval_description Total Applications
21004703Description131
11524709Description222
36005102Description320

table 2

queuedcsn_bypriority_cdrule_id1rule_id2rule_id3rule_id4rule_id5Total
123emp121504703150055102(blank)(blank)4
456emp21900028764703(blank)(blank)(blank)1
789emp119000470947035102(blank)(blank)3

etc...

Contributor
Posts: 29

Re: Merge

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

Super User
Posts: 5,362

Re: Merge

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 queueSmiley Happy;

   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.

Ask a Question
Discussion stats
  • 2 replies
  • 207 views
  • 0 likes
  • 3 in conversation