Hi All,
I have the following two data sets RETITLE and MATCH (sample)
retitle |
|
|
| match |
|
|
ID | date | time |
| ID | date | time |
1 | 18Jun2018 | 13:25:21 |
| 1 | 18Jun2018 | 13:25:21 |
2 | 28Jun2018 | 11:33:08 |
| 2 | 28Jun2018 | 11:33:08 |
4 | 29Jun2018 | 11:24:33 |
| 3 | 29Jun2018 | 14:13:40 |
6 | 29Jun2018 | 13:25:45 |
| 4 | 29Jun2018 | 11:24:33 |
8 | 21Jun2018 | 9:59:05 |
| 5 | 29Jun2018 | 12:05:34 |
|
|
|
| 6 | 29Jun2018 | 13:25:45 |
|
|
|
| 7 | 14Jun2018 | 8:25:34 |
|
|
|
| 8 | 23Jun2018 | 9.22.00 |
I am trying to create a table from these two data sets where the combination of ID, Date and Time are unique to RETITLE. In the above sample there would only be the one observation
8 | 21Jun2018 | 9:59:05 |
I was playing around with SQL but got stuck when considering the multiple variables.. I had the following code for a single variable but wanted to expand it to include date and time;
proc sql;
create table anysingles as
select *
from retitle
where id not in (select id from match);
quit;
any help would be great.
Exactly 3 variables in both datasets with the same names?
if yes,
proc sql;
create table want as
select * from retitle
except
select * from match;
quit;
Exactly 3 variables in both datasets with the same names?
if yes,
proc sql;
create table want as
select * from retitle
except
select * from match;
quit;
I find the following documentation quite helpful:
And here an (untested) data step approach:
data want2;
merge retitle match(in=inmatch);
by id date time;
if not inmatch;
run;
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.
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.