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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.