topic Re: Set two sas data sets side-by-side horizontally in SAS Programming

Set two sas data sets side-by-side horizontally

wlierman — Mon, 01 Nov 2021 21:02:26 GMT

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

Re: Set two sas data sets side-by-side horizontally

PaigeMiller — Mon, 01 Nov 2021 21:08:34 GMT

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.

Re: Set two sas data sets side-by-side horizontally

Kurt_Bremser — Mon, 01 Nov 2021 21:39:12 GMT

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.

Re: Set two sas data sets side-by-side horizontally

wlierman — Mon, 01 Nov 2021 21:57:21 GMT

The merge created the dataset I was hoping for.

Thank you.

wlierman

Re: Set two sas data sets side-by-side horizontally

SASified — Mon, 01 Nov 2021 23:58:27 GMT

The solution is as simple as this:

data merged_tables;
  merge table1 table2;
run;

This will create the vertical join as intended.

Re: Set two sas data sets side-by-side horizontally

PeterClemmensen — Tue, 02 Nov 2021 06:57:33 GMT

The Merge Statement with no By Statement does this.