topic Re: How do i Creates one table from 2 tables in SAS Programming

How do i Creates one table from 2 tables

shlomiohana — Thu, 13 Aug 2020 21:39:25 GMT

Hello,

I have 2 tables:

Table A:

Branch_Area

220

Table B:

Branch_Id	Area
1	171
2	173
3	174
4	175

I want the final table to be a combination of the 2 tables.

The Branch_Area field from table A will appear as it is in the final table, the Area field from table B will be added to the final table according to the following logic:

Each record from table A should be searched in the Branch_Id field in table B, If there is a match the value from the Area field should be taken to the final table.

If there is no match, enter the same value that we searched, for example, the value 0 or 220.

Final table:

Area	Branch_Area
0	0
171	1
173	2
174	3
220	220

Thanks a lot.

Re: How do i Creates one table from 2 tables

ChrisNZ — Thu, 13 Aug 2020 23:06:51 GMT

Like this?

data C;
  merge A(in=A)
        B(in=B rename=(BRANCH_ID=BRANCH_AREA));
  by BRANCH_AREA;
  if A;
  if ^B then AREA=BRANCH_AREA;
run;

Obs	Branch_Area	Area
1	0	0
2	1	171
3	2	173
4	3	174
5	220	220

Re: How do i Creates one table from 2 tables

smantha — Thu, 13 Aug 2020 23:08:04 GMT

data table_a;
input Branch_area;
cards;
0
1
2
3
220
;;;
run;

data table_b;
input Branch_id area;
cards;
1	171
2	173
3	174
4	175
;;;
run;

proc sql;
create table c
as select Branch_Area, coalesce(Area,Branch_area) as Area
from Table_a b
left join table_b a
on a.branch_id = b.branch_area;
quit;