turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Merging tables

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-04-2017 11:09 PM

Hi, I am using SAS Enteprise Guide 5.1 and would like to know if anyone could tell me how to merge seven tables, all with the same columns, but I want to create a table that only pulls data that all seven tables have based on certain columns? I tried using the logic below but it doesnt seem to work. I am pretty sure the problem is happening in the "WHERE" statement in the "CREATE TABLE RESULTS" section. Any help would be greatly appreciated.

**PROC** **SQL**;

CREATE Table parta as

Select *

from step2

where TYPE IN ('A');

**quit**;

**PROC** **SQL**;

CREATE Table partb as

Select *

from step2

where TYPE IN ('B');

**quit**;

**PROC** **SQL**;

CREATE Table partc as

Select *

from step2

where TYPE IN ('C');

**quit**;

**PROC** **SQL**;

CREATE Table partd as

Select *

from step2

where TYPE IN ('D');

**quit**;

**PROC** **SQL**;

CREATE Table parte as

Select *

from step2

where TYPE IN ('E');

**quit**;

**PROC** **SQL**;

CREATE Table partf as

Select *

from step2

where TYPE IN ('F');

**quit**;

**PROC** **SQL**;

CREATE Table partg as

Select *

from step2

where TYPE IN ('G');

**quit**;

**PROC** **SQL**;

Create table results as

SELECT parta.*, partb.*, partc.*, partd.*, parte.*, partf.*, partg.*

FROM parta, partb, partc, partd, parte, partf, partg

WHERE parta.1 = partb.1 = partc.1 = partd.1 = parte.1 = partf.1 = partg.1

and parta.2 = partb.2 = partc.2 = partd.2 = parte.2 = partf.2 = partg.2

and parta.3 = partb.3 = partc.3 = partd.3 = parte.3 = partf.3 = partg.3

and parta.4 = partb.4 = partc.4 = partd.4 = parte.4 = partf.4 = partg.4;

**quit**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-04-2017 08:42 PM

Hi, I am using SAS Enteprise Guide 5.1 and would like to know if anyone could tell me how to merge seven tables, all with the same columns, but I want to create a table that only pulls data that all seven tables have based on certain columns? I tried using the logic below but it doesnt seem to work. I am pretty sure the problem is happening in the "WHERE" statement in the "CREATE TABLE RESULTS" section. Any help would be greatly appreciated.

**PROC** **SQL**;

CREATE Table parta as

Select *

from step2

where TYPE IN ('A');

**quit**;

**PROC** **SQL**;

CREATE Table partb as

Select *

from step2

where TYPE IN ('B');

**quit**;

**PROC** **SQL**;

CREATE Table partc as

Select *

from step2

where TYPE IN ('C');

**quit**;

**PROC** **SQL**;

CREATE Table partd as

Select *

from step2

where TYPE IN ('D');

**quit**;

**PROC** **SQL**;

CREATE Table parte as

Select *

from step2

where TYPE IN ('E');

**quit**;

**PROC** **SQL**;

CREATE Table partf as

Select *

from step2

where TYPE IN ('F');

**quit**;

**PROC** **SQL**;

CREATE Table partg as

Select *

from step2

where TYPE IN ('G');

**quit**;

**PROC** **SQL**;

Create table results as

SELECT parta.*, partb.*, partc.*, partd.*, parte.*, partf.*, partg.*

FROM parta, partb, partc, partd, parte, partf, partg

WHERE parta.1 = partb.1 = partc.1 = partd.1 = parte.1 = partf.1 = partg.1

and parta.2 = partb.2 = partc.2 = partd.2 = parte.2 = partf.2 = partg.2

and parta.3 = partb.3 = partc.3 = partd.3 = parte.3 = partf.3 = partg.3

and parta.4 = partb.4 = partc.4 = partd.4 = parte.4 = partf.4 = partg.4;

**quit**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-04-2017 10:07 PM

This would get more response if you posted it in the base sas programming forum, and include a sample data step, as well as another datastep showing the result you're looking to achieve. However, that said, surely you don 't have variables called 1,2,3,4.

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-04-2017 11:12 PM

Thanks for the response. I have m oved the question to the forum you suggested and no the variables are not 1, 2, 3, and 4. They are just a space holder

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 12:18 AM

An example of what step2 looks like, perferably in the form of a datastep, along with the result you'd like to obtain from that file, would definitely help us understand what you have and are trying to accomplish.

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 01:03 AM - edited 03-05-2017 01:24 AM

you can do it by one data step:

data want;

merge partA (where=(type='A'))

partB (where=(type='B'))

partC (where=(type='C'))

......

partG(where=(type='G'))

;

by key1 key2 key3 key4; /* equvalent to sql partA.1 partA.2 partA.3 partA.4 */

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 11:32 AM

Thanks Shmuel

That seemed to do the trick to a certain degree I used:

**PROC** **SQL**;

CREATE Table parta as

Select *

from step2

where TYPE IN ('A');

**quit**;

**PROC** **SQL**;

CREATE Table partb as

Select *

from step2

where TYPE IN ('B');

**quit**;

**PROC** **SQL**;

CREATE Table partc as

Select *

from step2

where TYPE IN ('C');

**quit**;

**PROC** **SQL**;

CREATE Table partd as

Select *

from step2

where TYPE IN ('D');

**quit**;

**PROC** **SQL**;

CREATE Table parte as

Select *

from step2

where TYPE IN ('E');

**quit**;

**PROC** **SQL**;

CREATE Table partf as

Select *

from step2

where TYPE IN ('F');

**quit**;

**PROC** **SQL**;

CREATE Table partg as

Select *

from step2

where TYPE IN ('G');

**quit**;

**DATA** want;

merge parta (where=(type='A'))

partb (where=(type='B'))

partc (where=(type='C'))

partd (where=(type='D'))

parte (where=(type='E'))

partf (where=(type='F'))

partg (where=(type='G'));

by key1 key2 key3 key4;

**run**;

**PROC** **SQL**;

CREATE Table results as

Select *

from want

**Quit**;

All tables were created, however, after the run I got the error message:

ERROR: BY variables are not properly sorted on data set WORK.partd which I dont understand since all the rest worked as it should have. This prevented the "RESULTS" table from being created

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 12:01 PM

No need of the 7 first sql steps if you don't need each part separately.

Just run:

`proc sort data=step2`

`(where=(type in ('A', 'B', 'C', 'D' 'E' 'F' 'G')))`

out=WANT;

by key1 key2 key3 key4;

run;

or using SQL:

`PROC SQL; CREATE Table want as Select * from step2 where TYPE in`

`('A', 'B', 'C', 'D', 'E', 'F', 'G')`

`order by key1, key2, key3, key4;`

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 01:43 PM

Thanks again Schmuel

That worked, however, I am getting the same results as if i had used the 7 seperate parts. It is not eliminating lines that do not have the 4 criteria in common. For instance, in the example below, there are 7 sets of data, each set has the same KEY1. But only the set highlighted in red are the only ones I am interested in because all 4 keys are identicle where as the others may have one or two keys that are identical but the other keys are not.

KEY1 | KEY2 | KEY3 | KEY4 |

XXX581800 | 01/05/16 | 34 | 043397450 |

XXX581800 | 01/29/16 | CF | 208550806 |

XXX585700 | 01/21/16 | 03 | 043397450 |

XXX585700 | 01/21/16 | 03 | 043397450 |

XXX589800 | 01/07/16 | 58 | 043397450 |

XXX589800 | 01/28/16 | 05 | 043397450 |

XXX591700 | 01/12/16 | 35 | 043397450 |

XXX591700 | 01/26/16 | 21 | 043397450 |

XXX618000 | 01/25/16 | 35 | 043397450 |

XXX618000 | 01/08/16 | 03 | 043397450 |

XXX624000 | 01/29/16 | 01 | 043584262 |

XXX624000 | 01/29/16 | 01 | 043584262 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 01:46 PM

By the way, there are many other columns in the dataset that do not need to be identical just the 4 keys

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 12:06 PM

The MERGE step didn't work because merge needs input datasets to be sorted.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 01:06 AM

In EG use the TEANSPOSE task instead of this.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 02:18 AM

Merged the two threads into one.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 12:51 PM

Without example have and want datasets, and an explanation of what you are trying to accomplish, I find this thread confusing and probably misleading.

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-05-2017 02:33 PM - edited 03-05-2017 06:14 PM

Now I understand what you're trying to. The following should do it and doesn't require your data to be sorted:

proc sql; create table want (drop=rec_count comp_count) as select *, count(*) as rec_count from have as a join ( select key1,key2,key3,key4,count(*) as comp_count from have group by key1,key2,key3,key4) as b on a.key1 = b.key1 group by a.key1 having rec_count eq comp_count ; quit;

Art, CEO, AnalystFinder.com