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.
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.
So ET is always missing in Table2? data data_set; set table_1 table_2; by TPMC Date; if first.Date; run;
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.
Simply add
if et not in ("."," ");
after the set statement.
This will return only a table which is similar like table1. all of table2 rows are not added.
@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.
it is returning a table with headers only. no data.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.