BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TheKurgen
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20

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;
TheKurgen
Calcite | Level 5
Thanks novinosrin, there are other variables in both data sets, but I think If I limit my data sets to these three varaibles this does the trick!
TheKurgen
Calcite | Level 5
Thanks novinosrin, there are other variables in both data sets, but I think If I limit my data sets to these three variables this does the trick!
Patrick
Opal | Level 21

@TheKurgen

I find the following documentation quite helpful:

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n0vo2lglyrnexwn14e...

 

And here an (untested) data step approach:

data want2;
  merge retitle match(in=inmatch);
  by id date time;
  if not inmatch;
run;
TheKurgen
Calcite | Level 5
Thanks Patrick, the link you provided was great and I will give the data step a shot

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 760 views
  • 0 likes
  • 3 in conversation