BookmarkSubscribeRSS Feed
wheddingsjr
Pyrite | Level 9

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;

27 REPLIES 27
wheddingsjr
Pyrite | Level 9

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;

art297
Opal | Level 21

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

 

wheddingsjr
Pyrite | Level 9

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

art297
Opal | Level 21

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

 

Shmuel
Garnet | Level 18

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;

wheddingsjr
Pyrite | Level 9

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

   

 

 

Shmuel
Garnet | Level 18

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;

 

 

wheddingsjr
Pyrite | Level 9

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

 

 

wheddingsjr
Pyrite | Level 9

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

Shmuel
Garnet | Level 18
The MERGE step didn't work because merge needs input datasets to be sorted.
Reeza
Super User

In EG use the TEANSPOSE task instead of this. 

art297
Opal | Level 21

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

 

art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 27 replies
  • 1370 views
  • 0 likes
  • 6 in conversation