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. For example in the table mentioned below, TYPE has been changed in the most recent report with reference to the earliest report submitted. 

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


data want ;
format date_1 date9. ;
input id $ sp_num $ type $  date_1 yymmdd8.;
datalines;
101 a01 b 20160103
103 c03 b 20160108
;
run;

Can anybody kindly guide me to get it. 

 

Thank you in advance. 

Regards,

 

Swain
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

I think I answered my own question. Are you looking to do something like?:

data want (drop=change);
  set have;
  by id sp_num;
  retain change;
  if first.sp_num then change=0;
  if type ne lag(type) and not first.sp_num then change=1;
  if change and last.sp_num then output;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

did you also want the following record in your want dataset?:

102 b02 b 20160105

Art, CEO, AnalystFinder.com

DeepakSwain
Pyrite | Level 9

Hi art297,

As the value of type has not changed, that record is not included.

Regards,

 

Swain
art297
Opal | Level 21

I think I answered my own question. Are you looking to do something like?:

data want (drop=change);
  set have;
  by id sp_num;
  retain change;
  if first.sp_num then change=0;
  if type ne lag(type) and not first.sp_num then change=1;
  if change and last.sp_num then output;
run;

Art, CEO, AnalystFinder.com

 

DeepakSwain
Pyrite | Level 9
Hi art297,
The advice given is great !
Can you advice me further to identify all records having change in TYPE with reference to the first one.
Regards,
Swain
art297
Opal | Level 21

In order to identify all instances where type is different than the initial value for type, I'd suggest a slightly different approach:

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

data want (drop=change);
  set have;
  by id sp_num;
  retain first_type;
  if first.sp_num then first_type=type;
  else if type ne first_type then output;
run;

Art, CEO, AnalystFinder.com

DeepakSwain
Pyrite | Level 9

Hi art297,

 

Thanks for taking the pain to answer my silly questions. It has been a good learning experience. 

 

Regards,

 

Swain

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
  • 6 replies
  • 1992 views
  • 1 like
  • 2 in conversation