topic Merge in SAS Enterprise Guide

Merge

jen123 — Tue, 18 Jun 2013 17:14:46 GMT

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...

Re: Merge

sunilzood — Tue, 18 Jun 2013 17:56:34 GMT

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

Re: Merge

Astounding — Tue, 18 Jun 2013 18:17:25 GMT

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;

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.