Comparing between dates across column and rows

[jonyu]

I have a data set  as follows:

Obs srecip plan_beg1 plan_beg2 plan_beg3 plan_beg4 plan_end1 plan_end2 plan_end3 plan_end4 

1 000019067904 06/03/2011 . . . 06/07/2011 . . .
2 000019067904 06/01/2012 . . . 06/30/2012 . . .

3 000001037504 07/01/2017 . . . 12/31/2017 . . .
4 000001037504 07/01/2017 . . . 06/30/2018 . . .

I need to find total days between dates by SRECIP but have problems dealing with overlaps so for the example above, 

for 000001037504, since there is overlap, I just want to compare between the minimum date and maximum date, i.e. there are 364 days between July 1, 2017 and June 30, 2018. For 000019067904, there is a gap between dates so I would just add difference in dates (June 1, 2012 - June 30, 2012) and (June 3, 201 - June 7, 2011) to have a total of 33 days. There are possibilities of having other date interval (i.e. plan_beg2 - plan_end2, etc) entries that would need to be added into the total amount of days that would need to check for overlaps and gaps. The logic is similar in that if there is overlap, to simply take total days between minimum date and maximum date of that interval.  

Any help would be greatly appreciated!

[noling · Accepted solution]

I would think you could do this in two datasteps:

1. Transpose your data - go from wide to skinny. This could look something like:

srecip       plan_beg         plan_end

srecip<n> plan_beg<m> plan_end<m> 

srecip<n> plan_beg<m> plan_end<m> 

srecip<n> plan_beg<m> plan_end<m> 

srecip<n> plan_beg<m> plan_end<m> 

where you have 1 <n> for each srecip and <m> goes from 1 to 4 (or however many plan_beg and plan_end's you have)

2. In your second pass you could retain earliest plan_beg and plan_end for each srecip, then calculate the differences.

I'm sure you could use proc sql or combine the two datasteps into a single datastep, but that would be a transparent approach. Make sense?

[jonyu]

That makes sense! I was able to use the set up you proposed and figure out the rest of the logic. Thank you so much for your kind help!

[ScottBass]

Edit your original post with self-contained data steps.  Don't make us do your work by forcing us to convert your post into usable code.