DATA Step, Macro, Functions and more

Identify distinct values by groups

Reply
Contributor
Posts: 52

Identify distinct values by groups

I have 3 variables in my longitudinal dataset: id, visit, and date.

 

ID        Visit          Date

1          1              01/01/90

1          1              01/01/90

1          2              01/01/90

1          2              02/01/90

2          1              01/01/90

2          1              01/02/90

2          1              01/02/90

3          1              02/01/90

3          1              02/02/90

 

 

 

 

Every id will have multiple observations per visit, but the dates need to be the same.

How can I output the id,visit numbers, and dates for which there are distinct dates per id and visit?

I would prefer using SQL.

 

 

ID        Visit          Date

1          2              01/01/90

1          2              02/01/90

2          1              01/01/90

2          1              01/02/90

2          1              01/02/90

3          1              02/01/90

3          1              02/02/90

Super User
Posts: 17,785

Re: Identify distinct values by groups

PROC SORT with the UNIQUEOUT is a good option.

Contributor
Posts: 52

Re: Identify distinct values by groups

I want to identify unique dates within a id-visit group- i am not sure uniqueout does that?

Super User
Posts: 17,785

Re: Identify distinct values by groups


Melk wrote:

I want to identify unique dates within a id-visit group- i am not sure uniqueout does that?


Try it. 

Super User
Posts: 17,785

Re: Identify distinct values by groups

ID 2 has duplicates but you included them? Your output doesn't match your request.

 

2          1              01/01/90

2          1              01/02/90

2          1              01/02/90

Contributor
Posts: 52

Re: Identify distinct values by groups

 I want to output patients with distinct values for date by visit and id (all the observations for that patient-id group, as long as 1 of the dates is different).

Super User
Posts: 17,785

Re: Identify distinct values by groups


Melk wrote:

 I want to output patients with distinct values for date by visit and id (all the observations for that patient-id group, as long as 1 of the dates is different).


Original Q:

How can I output the id,visit numbers, and dates for which there are distinct dates per id and visit?

 

 Those are obviously different questions. So the 'grouping' level is only ID? As long as a person has multiple dates in that record you want those records regardless of Visit? 

 

Contributor
Posts: 52

Re: Identify distinct values by groups

For which there are at least 2 distinct dates is what I meant. It doesnt matter what is output actually, as long as I can identify for which patient and visit number there existed multiple dates. Some observations  may have been on the same date which is ok is what i was getting at, i suppose i can go back and see from the original dataset how many observations that id had on that visit, although i would prefer to output all of them in the code used to identify them.

Super User
Posts: 10,487

Re: Identify distinct values by groups

Another way:

 

proc freq data=have noprint;
   table id*visit*date/list nocum nopercent out=want(where=(count>1));
run;
Contributor
Posts: 52

Re: Identify distinct values by groups

grouping level is id + visit.

Super User
Posts: 17,785

Re: Identify distinct values by groups

Here's a solution. 

 

data have;
infile cards truncover;
informat id visit 8. date mmddyy10.;
format date date9.;
input id  visit date;
cards;
1 1 01/01/90
1 1 01/01/90
1 2 01/01/90
1 2 02/01/90
2 1 01/01/90
2 1 01/02/90
2 1 01/02/90
3 1 02/01/90
3 1 02/02/90
;
run;

proc sql;
create table want as
select * 
from have
group by id, visit
having max(date) ne min(date)
order by 1,2,3;
quit;
PROC Star
Posts: 252

Re: Identify distinct values by groups

This worked for me but there could be other better options as @Reeza pointed out.

 

proc sql;

select distinct id, visit, date from 
(select a.id, a.visit, coalesce(b.date, a.date) as date format =date9. from 
(select id, visit, date from yourtable)a
inner join
(select id, visit, date,count(*) as cnt from yourtable
group by 1,2,3)b
on a.id = b.id
and a.visit= b.visit
and a.date =b.date 
and cnt = 1
or(
a.id = b.id
and a.visit= b.visit
and a.date <>b.date 
and cnt>1))cc;
quit;
Contributor
Posts: 52

Re: Identify distinct values by groups

Thank you both. I could not get @Reeza soln to work for me, maybe something i a doing worng. @kiranv_ worked great.

Ask a Question
Discussion stats
  • 12 replies
  • 309 views
  • 2 likes
  • 4 in conversation