Best way to concatenate multiple rows of data that are in descending order

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Best way to concatenate multiple rows of data that are in descending order

Hello - I am trying to figure out how to concatenate character strings that are in order by account number and descending dates. I would like for the 'MemoCombo' field concatenate all data fields from 'MemeData' with the same 'CustIDKey' but reset when there is a new 'ACCT'.

ACCTCustIDKeyMemoDataDateMemoCombo
11146Did12/15/2013Did
11146you11/16/2013Did you
11146finish10/17/2013Did you finish
11146the test scripts?9/18/2013Did you finish the test scripts?
22266Did12/15/2013Did
22266you11/16/2013Did you
22266obtain final10/17/2013Did you obtain final
22266approval?9/18/2013Did you obtain final approval?

This is what I have tried thus far: I can get the count by CustIDKey but haven't figured out how to layer in the concatenation of the 'MemoData' field.

data CommentsPrep_c;

  set CommentsPrep_b;

  by CustIDKey;

  if first.CustIDKey then

  do;

  count = 0;

  end;

  count + 1;

  if last.CustIDKey then

  do;

  output;

  end;

run;


Accepted Solutions
Solution
‎12-19-2013 12:32 PM
Super User
Posts: 10,516

Re: Best way to concatenate multiple rows of data that are in descending order

You will want to add the highlighted parts below.

data CommentsPrep_c;

  set CommentsPrep_b;

  by CustIDKey;

  length MemoCombo $ 200; /* make the value at least as long as you expect the string to get*/

  Retain MemoCombo ;

  if first.CustIDKey then

  do;

  count = 0;

  MemoCombo = ''; /* reset to blank*/

  end;

MemoCombo = catx(' ',MemoCombo,MemoData);

  count + 1;

  if last.CustIDKey then

  do;

  output;

  end;

run;

View solution in original post


All Replies
Super User
Posts: 17,868

Re: Best way to concatenate multiple rows of data that are in descending order

Look up the RETAIN statement.

Solution
‎12-19-2013 12:32 PM
Super User
Posts: 10,516

Re: Best way to concatenate multiple rows of data that are in descending order

You will want to add the highlighted parts below.

data CommentsPrep_c;

  set CommentsPrep_b;

  by CustIDKey;

  length MemoCombo $ 200; /* make the value at least as long as you expect the string to get*/

  Retain MemoCombo ;

  if first.CustIDKey then

  do;

  count = 0;

  MemoCombo = ''; /* reset to blank*/

  end;

MemoCombo = catx(' ',MemoCombo,MemoData);

  count + 1;

  if last.CustIDKey then

  do;

  output;

  end;

run;

Contributor
Posts: 29

Re: Best way to concatenate multiple rows of data that are in descending order

Awsome!. It worked perfectly.I originally had a retain statement but couldn't get it to work. I appreciate the response

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 200 views
  • 0 likes
  • 3 in conversation