DATA Step, Macro, Functions and more

Adding another row based on condition

Reply
Occasional Contributor
Posts: 6

Adding another row based on condition

[ Edited ]

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.

PROC Star
Posts: 1,400

Re: Adding another row based on condition

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 Smiley Happy

Occasional Contributor
Posts: 6

Re: Adding another row based on condition

Thanks... I've added the Output
Super User
Super User
Posts: 9,829

Re: Adding another row based on condition

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;
  
  
PROC Star
Posts: 1,400

Re: Adding another row based on condition

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;
Ask a Question
Discussion stats
  • 4 replies
  • 172 views
  • 0 likes
  • 3 in conversation