Combine tables based on list of values with no exact match

[bon0]

Hello guys,

can you please advise with the following?

I need to combine multiple tables based on list of values with the following structure:

Table1		Table2		Desired output
Col1		Col2	Col3		Col1	Col4
A		BBB	5		A	90
B		CCC	10		B	40
C		AA	15		C	10
		AAAA	20
		AAA	25
		A	30
		B1	35

that means that in table1 I have list of values and I need to sum quantity from Col3 but there is no exact match with Col1 and Col2.

Thanks in advance

Jan

[PeterClemmensen]

Assuming your example data is representative, do something like this

data Table1;
input Col1 $ @@;
datalines;
A B C
;

data Table2;
input Col2 $ Col3;
datalines;
BBB 5
CCC 10
AA 15
AAAA 20
AAA 25
A 30
B1 35
;

proc sql;
   create table want as
   select Col1
         ,sum(Col3) as Col4
   from Table1, Table2
   where Col1=substr(compress(Col2, , 'd'), 1, 1)
   group by Col1;
quit;

[bon0]

Thank you but is there some way how to do this dynamically as the list of data and the results can change and I need to replicate similar or more complex logic for multiple tables.

[RW9]

Please supply test data/required output which illustrates the problem, theoretical problems are very hard to solve 🐵

[PeterClemmensen]

You have to be more specific than this. How is the logic more complex in your actual problem? 

[bon0]

Suppose we have the following tables:

Table 1 - list of regions - exported from the source, name of regions might change:

Table1

Col1

Americas

Americas_US

Americas_US_West

Americas_Canada

Americas_Canada_North

Table 2 - list of regions with monthly product sales

Table 2
Col2	Quantity
Americas_US_East_Sales	10
Americas_US_East_CS	20
Americas_US_East_Sales_Team	30
Americas_Canada	40
Americas_US_West	50

Desired output should be table which aggregates the sales per regions and subregions based on the list from Table1

= sum quantity from table 2 where col2 contains value from col1...

Desired Output - Table 3
Col1	Quantity
Americas	150
Americas_US	110
Americas_US_West	50
Americas_Canada	40
Americas_Canada_North	0

[RW9]

Well, from the specific example (and please note how I put test data in the form of a datastep!):

data table1;
  length col1 $200;
  input Col1 $;
datalines;
Americas
Americas_US
Americas_US_West
Americas_Canada
Americas_Canada_North
;
run;
data table2;
  length col2 $200;
  input Col2  $ Quantity;
datalines;
Americas_US_East_Sales   10
Americas_US_East_CS   20
Americas_US_East_Sales_Team   30
Americas_Canada   40
Americas_US_West   50
;
run;

proc sql;
  alter table table1 add quantity num;
  update table1
  set quantity=(select sum(this.quantity) from table2 this where substr(this.col2,1,lengthn(col1))=col1);
quit;

[Ksharp · Accepted solution]

How about this one ?

data Table1;
input Col1 $40.;
datalines;
Americas
Americas_US
Americas_US_West
Americas_Canada
Americas_Canada_North
;

data Table2;
input Col2 : $40. Col3;
datalines;
Americas_US_East_Sales	10
Americas_US_East_CS	20
Americas_US_East_Sales_Team	30
Americas_Canada	40
Americas_US_West	50
;

proc sql;
create table want as
 select Col1,coalesce(sum(Col3),0) as sum
  from table1 as a left join table2 as b
   on Col2 like cats(Col1,'%')
    group by Col1;
quit;

[bon0]

Is there some way to avoid the datalines as it has to be done manually, right and it is not what I need as I want to avoid any manual input to the code in future...

[RW9]

The datalines step is there to show test data that we can run on our machines.  We do not have access to your computer, or data, so it has to be created by us, providing datastep with datalines provides a means for us to have some data to work with, which is essential considering SAS is a data based language:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...