topic Re: SQL joins in SAS Programming

SQL joins

RamKumar — Sat, 23 Aug 2014 15:56:14 GMT

I've a table like below. I need to compare the tables (Table A & Table B) and I need to display the output only if table B value is plus or minus 150 when it is compared with the respective record of Table A.

I tried this task with inner join and similar stuff but it never helped. Please suggest.

Table A:

cost

1000

1020

1100

345

5678

12670

Table B:

cost

1150

970

2345

700

5500

11000

I need the output like below.

cost

1150

970

5500

Re: SQL joins

stat_sas — Sat, 23 Aug 2014 21:53:42 GMT

data table_A;
input cost;
id+1;
datalines;
1000
1020
1100
345
5678
12670
;

data Table_B;
input cost;
id+1;
datalines;
1150
970
2345
700
5500
11000
;

proc sql;

select b.cost from table_b b inner join table_a a

on b.id=a.id

and abs(b.cost-a.cost)<=150;

quit;

Re: SQL joins

PGStats — Sun, 24 Aug 2014 03:56:43 GMT

Operations that depend on the order of observations are simpler to do with datasteps. If you don't want to generate Id's, do as follows:

data table_A;

input cost;

datalines;

1000

1020

1100

345

5678

12670

;

data Table_B;

input cost;

datalines;

1150

970

2345

700

5500

11000

;

data Table_C;

set Table_A(rename=cost=cost_A);

set Table_B;

if abs(cost-cost_A) <= 150;

drop cost_A;

run;

Note, 5500 is not part of the result set.