If variable length greater 32767 then How to keep the remaining character in different variable?

[benhaz]

Hi ,

I am trying to concatenate all the rows from each group. I have written a code from which I am getting my desire result in last line(using last.) but problem is , it is only keeping up to 32767 characters. I am looking for a solution, if the character limit reaches 32767 then in the next variable (column) the remaining character should add. I am using the below code but it is not spiting into different variable if limit reaches. I have attached the result. Any help will be appreciated. Many thanks in advance.

data part4 (keep=DOC_NUMBER original_variable count);
set part3;
BY DOC_NUMBER;
if FIRST.DOC_NUMBER then
Count = 0;
Count + 1;
run;

data part5;
length concatenated_field $ 32767;
retain concatenated_field;
set part4;
by DOC_NUMBER;
if first.DOC_NUMBER then
do;
concatenated_field = original_variable;
end;
else
do;
concatenated_field = catx(', ', concatenated_field, original_variable);
end;
run;

[benhaz]

Hi ,

I am trying to concatenate all the rows from each group. I have written a code from which I am getting my desire result in last line(using last.) but problem is , it is only keeping up to 32767 characters. I am looking for a solution, if the character limit reaches 32767 then in the next variable (column) the remaining character should add. I am using the below code but it is not spiting into different variable if limit reaches. I have attached the result. Any help will be appreciated. Many thanks in advance.

data part4 (keep=DOC_NUMBER original_variable count);
set part3;
BY DOC_NUMBER;
if FIRST.DOC_NUMBER then
Count = 0;
Count + 1;
run;

data part5;
length concatenated_field $ 32767;
retain concatenated_field;
set part4;
by DOC_NUMBER;
if first.DOC_NUMBER then
do;
concatenated_field = original_variable;
end;
else
do;
concatenated_field = catx(', ', concatenated_field, original_variable);
end;
run;

[AhmedAl_Attar]

Which version of SAS software do you have?

If you have SAS 9.4 M5 and above, then you can use a variable of type VARCHAR, which could go beyond the 32767 max length of the Char type.

Check the LENGTH Statement  

Hope this helps,

Ahmed

[ballardw · Accepted solution]

Maybe something like this, completely untested because no actual data was provided (PDF does not count as data, I can't write code to use it).

data part5;
   length concatenated_field concatenated_field2 $ 32767;
   retain concatenated_field concatenated_field2;
   set part4;
   by DOC_NUMBER;
   if first.DOC_NUMBER then  do;
      concatenated_field = original_variable;
      call missing(concatenated_field2);
   end;
   else do;
      if length(concatenated_field) + length(orginal_variable) le 32767 
         and missing(concatenated_field2) then
      concatenated_field = catx(', ', concatenated_field, original_variable);
      else concatenated_field2=  catx(', ', concatenated_field2, original_variable);
   end;
run;

As always, I will ask: Why do you think you need that concatenated value at all? It is very likely to be extremely difficult to do anything with.

And what is the PDF attachment supposed to tell us? It doesn't show anything approaching the maximum length and the circled "Group 1" and "Group 2" are not discussed at all in your question.

[benhaz]

Hello Sir,

This is working as I wanted. But only one concern if I wanted to split in 3 variable like 

concatenated_field concatenated_field2 concatenated_field3

then where should I edit?

Thank you! 

[ballardw]

---

@benhaz wrote:

Hello Sir,

This is working as I wanted. But only one concern if I wanted to split in 3 variable like 

concatenated_field concatenated_field2 concatenated_field3

then where should I edit?

Thank you! 

---

And there we have one reason why this is likely to be a poor idea in general. If you need 3 variables of 32,000+ characters I suspect that you have a need in the future for 4 or 5. And the code gets more complex at each additional variable.

For example: Do you know why the check for Missing is included here:

if length(concatenated_field) + length(orginal_variable) le 32767 and missing(concatenated_field2) then

The reason is because we are checking a total length of the first concatenated plus that of the current. Missing value for the second concatenated variable means that we want to add to the first but once we start adding things to the second we want to keep adding them. Without a lot of knowledge about your actual values this sort of check is needed to prevent having a short value for original_variable that would fit into the 32767 but would be concatenated out of order. So you have to replicate additional checks for each and every other "concatenated variable" you add. With additional If/then/else blocks.

You still have not provided any actual use for one, much less 3 variables like this. If you let use know why we may have better suggestions.

[DavePrinsloo]

Why do you want all the text in a single column? Maybe there is a better solution to your real problem, rather than making a new problem caused by the maximum column length

[AllanBowe]

Just use LUA, where there are no limits!  (just memory limits).

Here's an example of creating a variable which is 100k characters wide:

proc lua;
submit;
	local demo
	local example='A'
	local a=1
	repeat
	   example=example..'1234567890'
	   a = a + 1
	until( a >100000 )
	print(string.len(example))
endsubmit;
quit;