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;
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;
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
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
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
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;
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
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;
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 |
By the way, there are many other columns in the dataset that do not need to be identical just the 4 keys
In EG use the TEANSPOSE task instead of this.
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
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
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.