topic Re: How to Merge / Combine two tables using PROC SQL or Data Merge or Combine in SAS Enterprise Guide

How to Merge / Combine two tables using PROC SQL or Data Merge or Combine

imanojkumar1 — Sun, 13 Nov 2016 21:42:37 GMT

I have the following table_1 :

TPMC	PWC	PWSC	Site	ET	Date	Time	DIAM	PXMC	SF
7101	7101	US000521	1	Lathing	08Nov2016	11:58	890.3		1
7102	7102	US000361	1	Lathing	02Nov2016	13:01	878.1		1
7102	7102	UC000348	2	Lathing	07Nov2016	18:22	877.3		1
7106	7106	UC00424	1	Lathing	05Oct2016	9:43	890,4		1
7106	7106	UC00437	3	Lathing	07Nov2016	18:23	877.1		1
7106	7106	UC309	4	Lathing	07Nov2016	18:26	877.8		1
7107	7107	UC05327	1	Lathing	06Oct2016	8:41	837		1
7107	7107	UC200	2	Lathing	13Oct2016	12:53	890.55		1
7108	7108	UC000361	3	Lathing	02Nov2016	13:01	878.1		1
7108	7108	UC00432	1	Lathing	07Nov2016	18:25	877.8		1
7108	7108	UC106	2	Lathing	03Oct2016	9:37	890.3		1

and table_2 :

TPMC	PWC	PWSC	Site	ET	Date	Time	DIAM	PXMC	SF
7101	.	.	.	.	01JAN16	.	.	.	.
7101	.	.	.	.	02JAN16	.	.	.	.
.	.	.	.	.	.	.	.	.	.
.	.	.	.	.	.	.	.	.	.
.	.	.	.	.	.	.	.	.	.
7101	.	.	.	.	30DEC16	.	.	.	.
7101	.	.	.	.	31DEC16	.	.	.	.
7102	.	.	.	.	01JAN16	.	.	.	.
7102	.	.	.	.	02JAN16	.	.	.	.
.	.	.	.	.	.	.	.	.	.
.	.	.	.	.	.	.	.	.	.
.	.	.	.	.	.	.	.	.	.
7102	.	.	.	.	30DEC16	.	.	.	.
7102	.	.	.	.	31DEC16	.	.	.	.

I want to merge two tables in a way that the output should look like something:

TPMC	PWC	PWSC	Site	ET	Date	Time	DIAM	PXMC	SF
7101	.	.	.	.	01JAN16	.	.	.	.
7101	.	.	.	.	02JAN16	.	.	.	.
.	.	.	.	.	.	.	.	.	.
7101	7101	US000521	1	Lathing	08Nov2016	11:58	890.3		1
.	.	.	.	.	.	.	.	.	.
.	.	.	.	.	.	.	.	.	.
7101	.	.	.	.	30DEC16	.	.	.	.
7101	.	.	.	.	31DEC16	.	.	.	.
7102	.	.	.	.	01JAN16	.	.	.	.
7102	.	.	.	.	02JAN16	.	.	.	.
.	.	.	.	.	.	.	.	.	.
7102	7102	US000361	1	Lathing	02Nov2016	13:01	878.1		1
7102	7102	UC000348	2	Lathing	07Nov2016	18:22	877.3		1
.	.	.	.	.	.	.	.	.	.
.	.	.	.	.	.	.	.	.	.
7102	.	.	.	.	30DEC16	.	.	.	.
7102	.	.	.	.	31DEC16	.	.	.	.

How can it be done using 'Proc SQL' or 'Data Merge' or 'Combine'?

I want to learn all three methods for future use.

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or Combine

imanojkumar1 — Sun, 13 Nov 2016 23:28:58 GMT

In the simplest form I used:

data data_set;
	set table_1 table_2;
run;

But this produced duplicate values of dates. For example:

TPMC	PWC	ET	PWSC	Site	Date	Time	DIAM	PXMC	SF
7618	.	.	.	1	29SEP2016		.	.	.
7618	.	.	UC00424	2	30SEP2016		.	.	.
7618	.	Lathing	UC00437	1	30SEP2016	17:15	890.500000	.	.
7618	.	Lathing	UC309	2	30SEP2016	20:32	890.500000	.	.
7618	.	.	.	3	01OCT2016		.	.	.
7618	.	.	.	1	02OCT2016		.	.	.

I don't know how can I avoid this. I do not want rows where there is no 'ET' (i.e. ET is '.' or empty, I do not want those rows).

So in the final table there will not be the line which is 'BOLD' in above output sample. And similarly, all other 'such rows', in final output table, will not be there as well.

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or Combine

Ksharp — Mon, 14 Nov 2016 03:21:04 GMT

So ET is always missing in Table2?

data data_set;
	set table_1 table_2;
       by TPMC Date;
      if first.Date;
run;

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or Combine

imanojkumar1 — Mon, 14 Nov 2016 07:12:36 GMT

Thanks for helping.

Yes second table has only missing data. but may be later in future table 2 might have data. as of now, it is missing only.

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or Combine

Kurt_Bremser — Mon, 14 Nov 2016 07:52:20 GMT

Simply add

if et not in ("."," ");

after the set statement.

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or Combine

imanojkumar1 — Mon, 14 Nov 2016 08:52:48 GMT

This will return only a table which is similar like table1. all of table2 rows are not added.

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or Combine

imanojkumar1 — Mon, 14 Nov 2016 08:53:37 GMT

it is returning a table with headers only. no data.

Re: How to Merge / Combine two tables using PROC SQL or Data Merge or Combine

Kurt_Bremser — Mon, 14 Nov 2016 09:02:52 GMT

@imanojkumar1 wrote:

This will return only a table which is similar like table1. all of table2 rows are not added.

If ET is always missing in table2, then this is clearly a consequence of your requirement, quote:

"I do not want rows where there is no 'ET' (i.e. ET is '.' or empty, I do not want those rows)."

You might want to review your requirements and post a clear and concise rule which observations from the concatenation of the datasets you want.

And if you do not want to concatenate the datasets, but merge observations, then we need another method altogether.

Please post a sample of your input datasets and the wanted dataset, as it results from these example datasets.

Your initial example has data in the "want" dataset that is not present in the "have" datasets.