topic Re: Issue with merging in New SAS User

Issue with merging

Abhiraj — Wed, 08 May 2019 09:36:20 GMT

I am facing an issue with merging.

i have two data sets -

1) unmapped - having account numbers which are not mapped to any manager along with branch code (brcode)

2)managersFinal - this data set contains list of available managers along with their branch code. Since 1 manager can hold multiple accounts, therefore their names are repeated as many times.

other info:

there are more than 100 branches - brcode ranges from 1-150
unmapped account can only be assigned to manager of same branch
output dataset should contain only those accounts which are mapped

the code that i am trying but not working is -

data assigned;
merge unmapped (in=a)  managersfinal (in=b);
by brcode;
if a;
run;

attached are images of datasets.

Re: Issue with merging

PeterClemmensen — Wed, 08 May 2019 12:04:32 GMT

What do you mean by 'not working'? Given you posted data, what do you expect the result to look like? And what does your code give you?

Re: Issue with merging

Abhiraj — Wed, 08 May 2019 13:18:12 GMT

Thanks for your reply. I am attaching datasets i am working on and output i am getting. I will explain what output i need.

dataset unmapped - contains branch code(brcode) and account id which arent mapped

dataset managersFinal - contains list of managers who can be assigned those accounts. this dataset contains names of managers multiple times, indicating those many more accounts can be assigned to them.

dataset assigned - the output i am getting when i execute following data step -

data icici.assigned;
merge unmapped (in=a)  managersfinal (in=b);
by brcode;
if  a and b ;
run;

issue with my output:

total observations for branch 1 in the unmapped dataset are 148

total managers that can be assigned account for branch 1 are 366

so output dataset should contain only 148 observations for branch 1

we need to merge datasets based on branch code value.

Expected output -

fin

Re: Issue with merging

ballardw — Wed, 08 May 2019 17:50:22 GMT

So, what does the desired output look like?

What role do the Count, Emp_name, Emp_Status or ECN variables in the assigned or managers data set actually play in this problem?

Hint: Reduce the problem example data to about 10 accounts in the unmapped.

Reduce the Assigned and manager data set to a similar size, have such so that some of the "unmapped" will stay unmapped and some will be assigned.

A small enough example that you can manually create and demonstrate (show us) the desired data set.

ONLY show the variables actually used/ need in the input and output.