topic Re: Merging tables in SAS Programming

Merging tables

wheddingsjr — Sun, 05 Mar 2017 04:09:10 GMT

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;

Merging tables

wheddingsjr — Sun, 05 Mar 2017 01:42:36 GMT

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;

Re: Merging tables

art297 — Sun, 05 Mar 2017 03:07:39 GMT

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

Re: Merging tables

wheddingsjr — Sun, 05 Mar 2017 04:12:39 GMT

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

Re: Merging tables

art297 — Sun, 05 Mar 2017 05:18:08 GMT

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

Re: Merging tables

Shmuel — Sun, 05 Mar 2017 06:24:38 GMT

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;

Re: Merging tables

Reeza — Sun, 05 Mar 2017 06:06:17 GMT

In EG use the TEANSPOSE task instead of this.

Re: Merging tables

Kurt_Bremser — Sun, 05 Mar 2017 07:18:03 GMT

Merged the two threads into one.

Re: Merging tables

wheddingsjr — Sun, 05 Mar 2017 16:32:03 GMT

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

Re: Merging tables

Shmuel — Sun, 05 Mar 2017 17:01:26 GMT

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;

Re: Merging tables

Shmuel — Sun, 05 Mar 2017 17:06:26 GMT

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

Re: Merging tables

art297 — Sun, 05 Mar 2017 17:51:08 GMT

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

Re: Merging tables

wheddingsjr — Sun, 05 Mar 2017 18:43:37 GMT

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

Re: Merging tables

wheddingsjr — Sun, 05 Mar 2017 18:46:20 GMT

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

Re: Merging tables

art297 — Sun, 05 Mar 2017 23:14:18 GMT

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

Re: Merging tables

wheddingsjr — Sun, 05 Mar 2017 19:51:35 GMT

Thanks art297

First let me apologize to everyone, I am green when it comes to SAS code so forgive my naivete, but this code is confusing to me, Usint this code means I do or do not have to seperate the "TYPE"? I am also confused by the "from have as a" statement as well as the " group by key1,key2,key3,key4) as b on a.key1 = b.key1" statement.

Re: Merging tables

mkeintz — Sun, 05 Mar 2017 20:35:56 GMT

@art297 I'm still under the impression that the OP wants one row with seven groups of variables for each key combination, not 7 rows per combo with one set of vars.

Re: Merging tables

art297 — Sun, 05 Mar 2017 23:17:12 GMT

You didn't show/mention type in your example dataset. Adding it would be simple.

The code simply looks for two things: (1) all instances sharing the same key1 values and (2) all instances sharing all four key values. It creates counts for each (i.e., rec_count and comp_count) and only selects those key1 values that have equal rec_count and comp_count values (i.e., don't have any records that have multiple key2, key3 and key4 values).

The from have as a and from have as b statements are simply referring to the two versions of your data (i.e., the first instance where it is getting the counts of all records that share the same key1 and type values AND referring to it later as file a, the other instance where it is getting the counts of all records that share the same key1, key2, key3, key4 and type values AND referring to it later as file b).

I'd think that the following modification of the code would do what you need regarding the type variable:

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,type,count(*) as comp_count
            from have
              where type in ('A','B','C','D','E','F','G')
                group by key1,key2,key3,key4,type) as b
       on a.key1 = b.key1 and a.type eq b.type
         group by a.key1,a.type
           having rec_count eq comp_count and
             type in ('A','B','C','D','E','F','G')
 ;
quit;

Art, CEO, AnalystFinder.com

Re: Merging tables

wheddingsjr — Sun, 05 Mar 2017 20:43:46 GMT

Thanks all

The types were used because i began the project thinking that it was best to seperate the types. The types are actually already in my dataset but thought it would be easier bumping them up against each other thats why I wanted to differentiate them but I am finding that its use is not really necessary in the grand scheme of things.

Re: Merging tables

art297 — Sun, 05 Mar 2017 20:44:19 GMT

@mkeintz, you could of course be correct, but that isn't what I gleaned from the OP's example. Of course, if my latest suggested code does what he/she wants, a simple proc transpose of the results would satisfy that requirement.

Art, CEO, AnalystFinder.com

p.s. Are you going to Orlando? I didn't think I was, but just got a nice surprise from SAS Canada and will, after all, be attending.