Concatenation with array

Reply
New Contributor
Posts: 2

Concatenation with array

[ Edited ]

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

 

IDDate1Date2first_datelast_datesegmentdaysbrk
10012/01/201512/31/201512/01/201512/31/2015000
10001/01/201601/31/201612/01/201501/31/2016110
10004/01/201604/30/201604/01/201604/30/20162611
10005/01/201605/31/201605/01/201605/31/2016310
10006/01/201606/30/201605/01/201606/30/2016310
10007/01/201607/31/201605/01/201607/31/2016310
10008/01/201608/31/201605/01/201608/31/2016310
10009/01/201609/30/201605/01/201609/30/2016310
10010/01/201610/31/201605/01/201610/31/2016310
10011/01/201611/30/201605/01/201611/30/2016310
10012/01/201612/31/201605/01/201612/31/2016310
10001/01/201701/31/201705/01/201601/31/2017310
10002/01/201702/28/201705/01/201602/28/2017310
10003/01/201703/31/201705/01/201603/31/2017310
10004/01/201704/30/201705/01/201604/30/2017310
10005/01/201705/31/201705/01/201605/31/2017310
10006/01/201706/30/201705/01/201606/30/2017310
10007/01/201707/31/201705/01/201607/31/2017310
10008/01/201708/31/201705/01/201608/31/2017310
10009/01/201709/30/201705/01/201609/30/2017310
10010/01/201710/31/201705/01/201610/31/2017300

 

Resulting table

 

IDDate1Date2first_datelast_datesegmentdaysbrk
10004/01/201604/30/201604/01/201604/30/20162611
10010/01/201710/31/201705/01/201610/31/2017300

 

Desired table

 

IDDate1Date2first_datelast_datesegmentdaysbrk
10012/01/201501/31/201612/01/201501/31/2016110
10004/01/201604/30/201604/01/201604/30/20162611
10010/01/201710/31/201705/01/201610/31/2017300
Super User
Posts: 23,237

Re: Concatenation with array

What's the logic for the kept records? Your code looks pretty verbose so I'm assuming it's not just trying to go from 'current' to 'desired' table based on what you've shown so far. 

 

Based on what you've shown, I don't see how it's not just the last per segment? In a second pass:

 

data want;
set have;

by id segment;

if last.segment;

run;

 


cdevil01 wrote:

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

 

Super User
Posts: 6,626

Re: Concatenation with array

One glitch that I see (can't work through whether it's the cause of what you observe) ... the word _CHARACTER_ within a DATA step refers to all the character variables that have been defined thus far.  Additional character variables defined later are not included.  So if the array X refers to newly created variables that don't already exist, those variables are not  part of the array VARS.  You would need to switch the order of the array statements, defining X before defining VARS, to make the X variables included in the VARS array.

New Contributor
Posts: 2

Re: Concatenation with array

[ Edited ]
Posted in reply to Astounding

Hi Reeza and Astounding, sorry for the late reply. I found "catx" to work with less coding. Since I already had the logic built in, I partitioned the data three times to get the first, middle and last records. Thank you all for your very helpful suggestions.

Ask a Question
Discussion stats
  • 3 replies
  • 240 views
  • 0 likes
  • 3 in conversation