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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.