I am in the process of trying to "join" two SAS data sets horizontally.
Data set 1 header row is
CaseID CaseStatus County Date
Data set 1 has 362,559 rows
Data set 2 header row is
Contact_Person_ID CaseStatus1 County_T Date1
Data set 2 has 167,054 rows
I want a horizontally set data set so the header looks like
CaseID CaseStatus County Date Contact_Person_ID CaseStatus1 County_T Date1
I was envisioning that the data set would have 362,559 rows (to accommodate the largest
data set Data set 1. Then Data set 2 with 167,054 would be "contained" within the 362,559.
So I ran this code
Proc sql;
Create Table SASCHART.CASES_CONTACTS_WANT AS
SELECT *
From SASCHART.Epi_Cases_Export, SASCHART.Arias_Contacts_Trace;
run;
But the query produced 129,664,449 rows before I killed the run.
How can I just put the two datasets side-by-side
Data set 1 rows Data set 2 rows
1 1
2 2
. .
. .
. .
200000 100,000
. .
. .
. 167,059
362559
Then there is another tricky step that comes after.
Thank you for your help.
wlierman
What you did is a particular feature of SQL called a cartesian join. What you intended to do is done with a MERGE in a data step.
How do you want to join these data sets?
There doesn't seem to be an ID variable that exists in both data set. If CaseID existed in both data sets, you could match on CaseID. But that's not the case.
What you did is a particular feature of SQL called a cartesian join. What you intended to do is done with a MERGE in a data step.
The merge created the dataset I was hoping for.
Thank you.
wlierman
The solution is as simple as this:
data merged_tables;
merge table1 table2;
run;
This will create the vertical join as intended.
The Merge Statement with no By Statement does this.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: