BookmarkSubscribeRSS Feed
jen123
Fluorite | Level 6

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

2 REPLIES 2
sunilzood
Calcite | Level 5

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
PROC Star

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.

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 2 replies
  • 739 views
  • 0 likes
  • 3 in conversation