BookmarkSubscribeRSS Feed
cybharg
Fluorite | Level 6

I have the following dataset:

NameAccount1Account2Account3Account5Account6
ABC2345678994735362537256894534254635636456324526453647325423642364
XYZ231254312563246532423643512425452345376424553423545226354245214

 

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:

NameAccountLength_Of_Account
ABC234567899 473536253725689426
ABC534254635636456 32452645364728
ABC32542364236412
XYZ231254312563 2465324236435127
XYZ24254523453764 24553423545227
XYZ2635424521411

 

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.

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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 🙂

cybharg
Fluorite | Level 6
Thanks... I've added the Output
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
  
  
PeterClemmensen
Tourmaline | Level 20

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

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
  • 4 replies
  • 3018 views
  • 0 likes
  • 3 in conversation