BookmarkSubscribeRSS Feed
Melk
Lapis Lazuli | Level 10

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

12 REPLIES 12
Reeza
Super User

PROC SORT with the UNIQUEOUT is a good option.

Melk
Lapis Lazuli | Level 10

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

Reeza
Super User

@Melk wrote:

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


Try it. 

Reeza
Super User

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

Melk
Lapis Lazuli | Level 10

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

Reeza
Super User

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

 

Melk
Lapis Lazuli | Level 10

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.

ballardw
Super User

Another way:

 

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

grouping level is id + visit.

Reeza
Super User

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;
kiranv_
Rhodochrosite | Level 12

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;
Melk
Lapis Lazuli | Level 10

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

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
  • 12 replies
  • 5862 views
  • 2 likes
  • 4 in conversation