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

Hi there,

 

For your kind information, I am trying to identify only those records which have been submitted with some changes along with the related earliest record for comparison. For example in the table mentioned below, TYPE has been changed in the most recent report with reference to the earliest report submitted in case of id 101 so it will be available in WANT table whereas id 103 will not be available in WANT table as earliest and latest value for TYPE is same.

data have ;
format date_1 date9. ;
input id $ sp_num $ type $  date_1 yymmdd8.;
datalines;101 a01 p 20160102
101 a01 b 20160102
101 a01 b 20160105
102 b02 b 20160104
102 b02 b 20160105
103 c03 p 20160106
103 c03 b 20160107
103 c03 p 20160108
104 d04 p 20160102
104 d04 b 20160104
;
run;


data want ;
format date_1 date9. ;
input id $ sp_num $ type $  date_1 yymmdd8.;
datalines;
101 a01 p 20160102
101 a01 b 20160105
104 d04 p 20160102
104 d04 b 20160104
;
run;

Can anybody kindly guide me to get it. 

 

Thank you in advance. 

Regards,

Swain
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have ;
input id $ sp_num $ type $  date_1 yymmdd8.;
format date_1 date9. ;
datalines;
101 a01 p 20160102
101 a01 b 20160103
101 a01 b 20160105
102 b02 b 20160104
102 b02 b 20160105
103 c03 p 20160106
103 c03 b 20160107
103 c03 p 20160108
104 d04 p 20160102
104 d04 b 20160104
;
run;
data temp;
 set have;
 by id;
 if first.id or last.id;
run;
proc sql;
create table want as
 select *
  from temp
   group by id
    having count(distinct type)=2;
quit;

View solution in original post

4 REPLIES 4
kiranv_
Rhodochrosite | Level 12

what happend when you similar min dates for more than one observation for an id. what happend when you similar max dates for more than one observation for an id.

 

DeepakSwain
Pyrite | Level 9

Hi kiranv_, 

the multiple records within same date will be sorted based on time stamp and even if changes are found within records within same date will be considered for flagging. 

 

Regards,

 

Swain
kiranv_
Rhodochrosite | Level 12

Modified data just to keep one min and max. I think this should work.

 

data have ;

input id $ sp_num $ type $  date_1 yymmdd8.;
format date_1 date9. ;
datalines;
101 a01 p 20160102
101 a01 b 20160103
101 a01 b 20160105
102 b02 b 20160104
102 b02 b 20160105
103 c03 p 20160106
103 c03 b 20160107
103 c03 p 20160108
104 d04 p 20160102
104 d04 b 20160104
;
run;

/*picking up min and max records*/
proc sql;
create table inter as
select *, monotonic() as rnum from want a
where date_1=(select  max(date_1) as max_d
               from want b
			   where a.id =b.id
			   group by id)
or date_1=(select  min(date_1) as max_d
               from want b
			   where a.id =b.id
			   group by id)
order by id, date_1;
quit;
/* picking up the rows which have change in type you can extent it to others if you want*/
proc sql;
create table want as 
select id, sp_num, type, date_1 from inter a
inner join
inter b 
on a.id = b.id
and a.type <> b.type
where (a.rnum+1 =b.rnum
or b.rnum +1 =a.rnum);
quit;
Ksharp
Super User
data have ;
input id $ sp_num $ type $  date_1 yymmdd8.;
format date_1 date9. ;
datalines;
101 a01 p 20160102
101 a01 b 20160103
101 a01 b 20160105
102 b02 b 20160104
102 b02 b 20160105
103 c03 p 20160106
103 c03 b 20160107
103 c03 p 20160108
104 d04 p 20160102
104 d04 b 20160104
;
run;
data temp;
 set have;
 by id;
 if first.id or last.id;
run;
proc sql;
create table want as
 select *
  from temp
   group by id
    having count(distinct type)=2;
quit;

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