inner join using key if condition

fjsouz — Mon, 01 Mar 2021 23:55:19 GMT

I need to join tables, but the key must be different depending on the conditions
Exemple

Given each type and salary the following fee must be charged

Table 1

Type	salary	FEE(%)
A	1000	1
A	2000	2
A	3000	2,5
B	1000	2
B	2000	3
C	2000	1

Table 2

cliente	type	salary
1	A	1500
2	B	1600
3	A	1000
4	B	3500
5	C	8000
6	C	2000

My Key is type and salary, but the fee the fee must be charged according to the previous range

Exemple
cliente 1 must pay 1 % because the salary is smaller then the second type 2000.

how can I create the condition of the key considering the range?

I tried "if  - then ", but I have many conditions (more than 1000, it would be unfeasible)

Re: inner join using key if condition

PGStats — Tue, 02 Mar 2021 04:36:41 GMT

A datastep solution:

data fees;
input type $ salary  FEE;
datalines;
A   1000    1
A   2000    2
A   3000    2.5
B   1000    2
B   2000    3
C   2000    1
;

data clients;
input cliente type $ salary;
datalines;
1   A   1500
2   B   1600
3   A   1000
4   B   3500
5   C   8000
6   C   2000
;

proc sort data=clients; by type salary;

data want;
merge fees (in=inFee) clients(in=inClients); by type salary;
retain f;
if inFee then f = fee;
if inClients;
drop fee; rename f=fee;
run;

proc sort data=want; by cliente; run;

proc print data=want; run;

Note that you should have a record with a salary = 0 for each type to cover all cases.

topic inner join using key if condition in SAS Data Management

inner join using key if condition

Re: inner join using key if condition