topic Re: Concatenation with array in SAS Programming

Concatenation with array

cdevil01 — Sat, 11 Nov 2017 03:44:59 GMT

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;

* 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;

* 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;

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;

* 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

Re: Concatenation with array

Reeza — Sat, 11 Nov 2017 03:53:04 GMT

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

Re: Concatenation with array

Astounding — Sat, 11 Nov 2017 11:55:42 GMT

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.

Re: Concatenation with array

cdevil01 — Wed, 29 Nov 2017 22:25:22 GMT

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.