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...
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.