topic Re: Identify distinct values by groups in SAS Programming

Identify distinct values by groups

Melk — Thu, 08 Jun 2017 18:24:23 GMT

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

ID Visit Date

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

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

3 1 02/01/90

3 1 02/02/90

Re: Identify distinct values by groups

Reeza — Thu, 08 Jun 2017 18:36:22 GMT

PROC SORT with the UNIQUEOUT is a good option.

Re: Identify distinct values by groups

Melk — Thu, 08 Jun 2017 19:03:17 GMT

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

Re: Identify distinct values by groups

Reeza — Thu, 08 Jun 2017 19:05:59 GMT

@Melk wrote:

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

Try it.

Re: Identify distinct values by groups

Reeza — Thu, 08 Jun 2017 19:09:48 GMT

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

Re: Identify distinct values by groups

Melk — Thu, 08 Jun 2017 19:12:26 GMT

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).

Re: Identify distinct values by groups

Reeza — Thu, 08 Jun 2017 19:19:20 GMT

@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?

Re: Identify distinct values by groups

Melk — Thu, 08 Jun 2017 19:25:40 GMT

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.

Re: Identify distinct values by groups

Melk — Thu, 08 Jun 2017 19:26:49 GMT

grouping level is id + visit.

Re: Identify distinct values by groups

Reeza — Thu, 08 Jun 2017 19:37:35 GMT

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;

Re: Identify distinct values by groups

kiranv_ — Thu, 08 Jun 2017 20:42:24 GMT

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;

Re: Identify distinct values by groups

ballardw — Thu, 08 Jun 2017 21:39:45 GMT

Another way:

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

Re: Identify distinct values by groups

Melk — Thu, 08 Jun 2017 22:08:01 GMT

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