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
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;
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;
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.
Please supply test data/required output which illustrates the problem, theoretical problems are very hard to solve 🐵
You have to be more specific than this. How is the logic more complex in your actual problem?
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 |
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;
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;
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...
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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.