cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
cdevil01
Calcite | Level 5 cdevil01
Calcite | Level 5

Concatenation with array

Posted 11-10-2017 10:40 PM (993 views)

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
0 Likes
Reply
3 REPLIES 3
Reeza
Super User Reeza
Super User

Re: Concatenation with array

Posted 11-10-2017 10:53 PM (985 views)  |   In reply to cdevil01

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

 

0 Likes
Reply
Astounding
PROC Star Astounding
PROC Star

Re: Concatenation with array

Posted 11-11-2017 06:55 AM (912 views)  |   In reply to cdevil01

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.

0 Likes
Reply
cdevil01
Calcite | Level 5 cdevil01
Calcite | Level 5

Re: Concatenation with array

Posted 11-29-2017 05:23 PM (837 views)  |   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.

0 Likes
Reply

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

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
  • ‎11-10-2017 10:40 PM
  • 994 views
  • 0 likes
  • 3 in conversation