Hello, I am trying to collapse date segments based on a retained variable called first_date. The code below is skipping the segment=1: simplified current table, resulting table from code and desired table also included (array vars and nums are meant to retain other fields) any suggestions? data want; do until (eof); set work.have end=eof; by id Date1 Date2 segment; retain first_date last_date &t; format first_date last_date &t mmddyy10.; array vars (*) _character_; array nums (*) Date1 Date2; array x(*)$ &c; array y(*) &t; * initalize retained variables when id changes; if first.id then do; first_date=Date1 ; last_date =Date2 ; do i = 1 to dim(vars); x{i} = ''; end; do j = 1 to dim(nums); y{j} = .; end; end; * check segment value to determine what to do; if not first.id and not last.id then do; if segment=1 then do; last_date =Date2 ; do i = 1 to dim(vars); x{i} = vars{i}; end; do j = 1 to dim(nums); y{j} = nums{j}; end; end; * if segment ne 1 then output all the retained variables and repopulate retained variables; * with current record values; else do; first_date=Date1; last_date =Date2; do i = 1 to dim(vars); x{i} = vars{i}; end; do j = 1 to dim(nums); y{j} = nums{j}; end; output; do i = 1 to dim(vars); x{i} = vars{i}; end; do j = 1 to dim(nums); y{j} = nums{j}; end; first_date=Date1 ; last_date =Date2 ; end; end; if last.id then do; * if segment = 1 retain Date1 and update all other variables; if segment=1 then do; last_date =Date2 ; do i = 1 to dim(vars); x{i} = vars{i}; end; do j = 1 to dim(nums); y{j} = nums{j}; end; end; * if segment ne 1 then output all the retained variables and repopulate retained variables; * with current record values; else do; first_date=Date1 ; last_date = Date2 ; do i = 1 to dim(vars); x{i} = vars{i}; end; do j = 1 to dim(nums); y{j} = nums{j}; end; output; do i = 1 to dim(vars); x{i} = vars{i}; end; do j = 1 to dim(nums); y{j} = nums{j}; end; first_date=Date1 ; last_date =Date2 ; end; output; end;end; drop i j; run; Current table ID Date1 Date2 first_date last_date segment days brk 100 12/01/2015 12/31/2015 12/01/2015 12/31/2015 0 0 0 100 01/01/2016 01/31/2016 12/01/2015 01/31/2016 1 1 0 100 04/01/2016 04/30/2016 04/01/2016 04/30/2016 2 61 1 100 05/01/2016 05/31/2016 05/01/2016 05/31/2016 3 1 0 100 06/01/2016 06/30/2016 05/01/2016 06/30/2016 3 1 0 100 07/01/2016 07/31/2016 05/01/2016 07/31/2016 3 1 0 100 08/01/2016 08/31/2016 05/01/2016 08/31/2016 3 1 0 100 09/01/2016 09/30/2016 05/01/2016 09/30/2016 3 1 0 100 10/01/2016 10/31/2016 05/01/2016 10/31/2016 3 1 0 100 11/01/2016 11/30/2016 05/01/2016 11/30/2016 3 1 0 100 12/01/2016 12/31/2016 05/01/2016 12/31/2016 3 1 0 100 01/01/2017 01/31/2017 05/01/2016 01/31/2017 3 1 0 100 02/01/2017 02/28/2017 05/01/2016 02/28/2017 3 1 0 100 03/01/2017 03/31/2017 05/01/2016 03/31/2017 3 1 0 100 04/01/2017 04/30/2017 05/01/2016 04/30/2017 3 1 0 100 05/01/2017 05/31/2017 05/01/2016 05/31/2017 3 1 0 100 06/01/2017 06/30/2017 05/01/2016 06/30/2017 3 1 0 100 07/01/2017 07/31/2017 05/01/2016 07/31/2017 3 1 0 100 08/01/2017 08/31/2017 05/01/2016 08/31/2017 3 1 0 100 09/01/2017 09/30/2017 05/01/2016 09/30/2017 3 1 0 100 10/01/2017 10/31/2017 05/01/2016 10/31/2017 3 0 0 Resulting table ID Date1 Date2 first_date last_date segment days brk 100 04/01/2016 04/30/2016 04/01/2016 04/30/2016 2 61 1 100 10/01/2017 10/31/2017 05/01/2016 10/31/2017 3 0 0 Desired table ID Date1 Date2 first_date last_date segment days brk 100 12/01/2015 01/31/2016 12/01/2015 01/31/2016 1 1 0 100 04/01/2016 04/30/2016 04/01/2016 04/30/2016 2 61 1 100 10/01/2017 10/31/2017 05/01/2016 10/31/2017 3 0 0
... View more