DATA Step, Macro, Functions and more

Merging tables

Reply
Contributor
Posts: 24

Merging tables

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;

Contributor
Posts: 24

Merging tables

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;

PROC Star
Posts: 7,363

Re: Merging tables

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

 

Contributor
Posts: 24

Re: Merging tables

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

PROC Star
Posts: 7,363

Re: Merging tables

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

 

Trusted Advisor
Posts: 1,375

Re: Merging tables

[ Edited ]

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;

Contributor
Posts: 24

Re: Merging tables

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

   

 

 

Trusted Advisor
Posts: 1,375

Re: Merging tables

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;

 

 

Contributor
Posts: 24

Re: Merging tables

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.

 

KEY1KEY2KEY3KEY4
XXX58180001/05/1634043397450
XXX58180001/29/16CF208550806
XXX58570001/21/1603043397450
XXX58570001/21/1603043397450
XXX58980001/07/1658043397450
XXX58980001/28/1605043397450
XXX59170001/12/1635043397450
XXX59170001/26/1621043397450
XXX61800001/25/1635043397450
XXX61800001/08/1603043397450
XXX62400001/29/1601043584262
XXX62400001/29/1601043584262

 

 

Contributor
Posts: 24

Re: Merging tables

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

Trusted Advisor
Posts: 1,375

Re: Merging tables

The MERGE step didn't work because merge needs input datasets to be sorted.
Super User
Posts: 17,824

Re: Merging tables

In EG use the TEANSPOSE task instead of this. 

Super User
Posts: 6,936

Re: Merging tables

Merged the two threads into one.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 7,363

Re: Merging tables

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

 

PROC Star
Posts: 7,363

Re: Merging tables

[ Edited ]

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

 

Ask a Question
Discussion stats
  • 27 replies
  • 276 views
  • 0 likes
  • 6 in conversation