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

Hi,

I have following dataset 

data have;
infile datalines missover; 
   input lag24 mmddyy10. id date  mmddyy10. score;
   format date mmddyy10.;
   format lag24 mmddyy10.;
   datalines; 
           1 21-09-2009 1.4   
25-09-2007 1 25-09-2009 1.4
16-11-2007 1 16-11-2009 2.2
03-02-2008 1 03-02-2010 2.6
           2 21-09-2009 1.4   
25-09-2007 2 25-09-2009 1.4
16-11-2007 2 16-11-2009 2.2
03-02-2008 2 03-02-2010 2.6
;       
run;

I need the following data. We start with the last row within the group variable "ID", and check if the previous dates are within two years of the last date. Lets say the last date in the ID=1 is 03-02-2010 and the previous date is 16-11-2009 which is between 03-02-2008 and 03-02-2010, so take we sum of score (2.6+2.2). Then again check if the date before the previous date, ie. 2nd row from top 25-09-2009 is between 03-02-2008 and 03-02-2010, so take we sum of score (2.6+2.2+1.4) and again for the first row (2.6+2.2+1.41.4=7.6) which is diplayed on the last row. Similarly we take the 2nd last row as "index row" and check if the previous dates are between the date of index date and lag24=(index date-24 months).

 

 

data want;
infile datalines missover; 
   input lag24 mmddyy10. id date  mmddyy10. score sum;
   format date mmddyy10.;
   format lag24 mmddyy10.;
   datalines; 
           1 21-09-2009 1.4 
25-09-2007 1 25-09-2009 1.4 2.8
16-11-2007 1 16-11-2009 2.2 6.2
03-02-2008 1 03-02-2010 2.6 7.6
           2 21-09-2009 1.4   
25-09-2007 2 25-09-2009 1.4 2.8
16-11-2007 2 16-11-2009 2.2 5
03-02-2012 2 03-02-2014 2.6
;       
run;
1 ACCEPTED SOLUTION
3 REPLIES 3
ifti_ch2002
Obsidian | Level 7

One mistake. Date in Last row of data "have" is same as in data "want"

data have;
infile datalines missover; 
   input lag24 mmddyy10. id date  mmddyy10. score;
   format date mmddyy10.;
   format lag24 mmddyy10.;
   datalines; 
           1 21-09-2009 1.4   
25-09-2007 1 25-09-2009 1.4
16-11-2007 1 16-11-2009 2.2
03-02-2008 1 03-02-2010 2.6
           2 21-09-2009 1.4   
25-09-2007 2 25-09-2009 1.4
16-11-2007 2 16-11-2009 2.2
03-02-2012 2 03-02-2014 2.6
;       
run;
Kurt_Bremser
Super User

Please correct your data step code so that it works and delivers valid data. Your dates are (among other things) in  DMY order, so the MMDDYY informat won't work.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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