I have the following dataset:
Name | Account1 | Account2 | Account3 | Account5 | Account6 |
ABC | 234567899 | 4735362537256894 | 534254635636456 | 324526453647 | 325423642364 |
XYZ | 231254312563 | 24653242364351 | 24254523453764 | 245534235452 | 26354245214 |
I need to concatenate all the account columns into one column with space as delimiter. But the concatenated account field can't have more than 36 characters. When I am adding the accounts to the concatenated account field, if it's length exceeds 36 characters then I need to insert a new row with the same name and concatenate the rest of the accounts in the second row and so on.
The Output for the above dataset should look like this:
Name | Account | Length_Of_Account |
ABC | 234567899 4735362537256894 | 26 |
ABC | 534254635636456 324526453647 | 28 |
ABC | 325423642364 | 12 |
XYZ | 231254312563 24653242364351 | 27 |
XYZ | 24254523453764 245534235452 | 27 |
XYZ | 26354245214 | 11 |
The length column is just for reference - If the account3 was added to the concatenated variable for name ABC, then the length would exceed 36 - so a new row has to be created and the next accounts should be added in that row.
Please help.
I'm very new to SAS - I am able to concatenate using catx but I am not sure how to achieve the rest of it.
Thanks.
So the concatenated field is allowed to contain spaces? It would help a lot if you outline how your desired output data set looks just like you posted what data you have 🙂
This is a basic array and loop question, keep adding strings from the array until length is exceeded: Post test data in the form of a datastep to get working code, this is just and example (as I am not here to type test data in for you!!).
data want (keep=name account); length account $36; set have; array account{6}; do i=1 to 6; if lengthn(catx(" ",account,account{i})) > 36 then do; output; account=account{i}; end; else account=catx(" ",account,account{i}); end; run;
Not pretty but does the job
data have;
input Name$ Account1:$36. Account2:$36. Account3:$36. Account5:$36. Account6:$36.;
datalines;
ABC 234567899 4735362537256894 534254635636456 324526453647 325423642364
XYZ 231254312563 24653242364351 24254523453764 245534235452 26354245214
;
data want(keep= Name Account);
set have;
length Account $100;
array acc{*} Account1 Account2 Account3 Account5 Account6;
l=0;
do i=1 to dim(acc);
l=l+length(acc[i]);
if l<36 then do;
Account=catx(' ', Account, acc[i]);
end;
else do;
i=i-1;
output;
l=0;
Account='';
end;
if i=dim(acc) then output;
end;
retain l;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.