topic Combine tables based on list of values with no exact match in SAS Programming

Combine tables based on list of values with no exact match

bon0 — Wed, 12 Sep 2018 13:09:03 GMT

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

Re: Combine tables based on list of values with no exact match

PeterClemmensen — Wed, 12 Sep 2018 13:20:08 GMT

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;

Re: Combine tables based on list of values with no exact match

bon0 — Wed, 12 Sep 2018 13:29:24 GMT

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.

Re: Combine tables based on list of values with no exact match

RW9 — Wed, 12 Sep 2018 13:34:29 GMT

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

Re: Combine tables based on list of values with no exact match

PeterClemmensen — Wed, 12 Sep 2018 13:36:19 GMT

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

Re: Combine tables based on list of values with no exact match

Ksharp — Wed, 12 Sep 2018 13:58:42 GMT

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;

Re: Combine tables based on list of values with no exact match

bon0 — Wed, 12 Sep 2018 13:51:48 GMT

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

Re: Combine tables based on list of values with no exact match

bon0 — Wed, 12 Sep 2018 14:02:43 GMT

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...

Re: Combine tables based on list of values with no exact match

RW9 — Wed, 12 Sep 2018 14:04:59 GMT

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;

Re: Combine tables based on list of values with no exact match

RW9 — Wed, 12 Sep 2018 14:06:34 GMT

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-data-AKA-generate/ta-p/258712