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
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;
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.
PG
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.