topic Re: Adding another row based on condition in SAS Programming

Adding another row based on condition

cybharg — Thu, 16 Nov 2017 08:24:46 GMT

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.

Re: Adding another row based on condition

PeterClemmensen — Thu, 16 Nov 2017 08:08:23 GMT

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 🙂

Re: Adding another row based on condition

cybharg — Thu, 16 Nov 2017 08:21:37 GMT

Thanks... I've added the Output

Re: Adding another row based on condition

RW9 — Thu, 16 Nov 2017 08:58:34 GMT

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;

Re: Adding another row based on condition

PeterClemmensen — Thu, 16 Nov 2017 10:47:16 GMT

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;