How to append a character string more than 32767 in one variable

[rajdeep]

data D2;
length cat $32690;
do until (last.l);
set D1;
by l notsorted;
cat=catx(' ',cat,strip(sas_code)); 
end;
run;

Hi Folks,

I am trying to append observations of a sas dataset into one character variable. Now, the varible length is going beyond 32767 in length, so it's trimming the chracters after that. 

Please suggest me how to store the remaining character strings after 32767 position into other variable.

For example I have one proc sql; query which is having lot of case when statements and those statements I am trying to append into a single line one after another, but problem is once it's reaching to 32767 characters then it's not appending further.(in the given code above, consider the sas_code is having the big query and l is the grouping of line numbers based on proc and quit as start and end point)

Please help me how to store the remaing case when statements after 32767 characters into another variable, because I need the complete proc sql; till quit; query.

Thanks in advance.

[Tom]

Why? What is wrong with storing the code in multiple observations?

[rajdeep]

Hi Tom,

Thanks for replying. I need the multiple observation into a single line becasue I need to capture sas query between proc and quit, same as between data and run. So that I can pull the required information from the query with help of string funtions available in SAS. 

For example if I am reading the query line by line then few case when statements woulb have been bifurcated into more than one line, so if I can combine all the line into one then I can retriev the complete query.

Hope this information will help.

[Shmuel]

I'm not sure that I understand what kind of data you are dealing with.

Quoting from your post:

  "cat=catx(' ',cat,strip(sas_code));"

   "I am trying to append observations of a sas dataset ".

   "For example if I am reading the query line by line then few case when statements

     woulb have been bifurcated into more than one line".

According to quoted post lines it seems that your sas dataset is actualy a sas program. Isn't it ?

If it is then try create a dataset with two varaibles:

(1) sequnce nomber (2) a variable each containing one statement ending with a semicolon.

I believe you are looking for case combinations taken from one statement only (spread in one or more lines)

and not from severeal statements.

If it is a program, don't you have it as a text .sas file ?

If positive read it with INFILE and dlm=';'.

[rajdeep]

Hi,

Yes My input is SAS code itself. The infile statement will read line by line & we need data in concatenated manner.

Thanks

[Shmuel]

Can you post the cases you are looking for by sql ?

Are you looking for strings in different sas statemnets ?

[Oligolas]

Hi,

something like this?

%macro doit;
   %macro t;%mend t;
   PROC SQL noprint;
      select ceil(sum(length(name))/50)  into:nvars TRIMMED
      from sashelp.class
      ;
   QUIT;

   DATA D2;
      length len 8;
      %do i=1 %to &nvars.;
         length cat&i. $32690;
         retain cat&i. '';
      %end;
      set sashelp.class;
      retain len 0 varcount 1;
      len=len+length(name);

      if len>50 then do;
         len=length(name);
         varCount=varCount+1;
      end;

      array vars(*) cat1-cat&nvars.;
      do i=varCount to varCount;
         vars(i)=catx(' ',vars(i),strip(name)); ;
      end;
   RUN;

%mend doit;
%doit;

[rajdeep]

Thanks oligoals for your effort. I am just wondering if there are texts between 2 specific keywords will it pull the data based on that!!!

Like for example: if I have something like 

hello

xyz

xyz

xuz

xyuz

xyz

wordl;

Hi

abc

abc

abc

abc

there;

Will the code will append the text between hello and world including both hello and world.(hello xyz xyz xyz xyz world) and (hi abc abc abc abc there)?

Like in the terms of first. and last. manner of grouping between 2 keywords.

[Oligolas]

Hi,

it will try to append whatever is in between 'Hello' and 'there;' into one variable.

If the appending results in a variable length that exceeds what has been specified here:

len>50

then a new variable is created. (according to your first post, you want 32690 here)

You have the code give it a try.

[rajdeep]

Hi,

If there is a string which is greater than 32767 in length, so  is it possible in SAS to split the string till 32767 in to one variable and from 32768 to another variable??

 Please help me if possible or just let me know how we can achieve this?

Thanks in advance.

[benhaz]

Hi @Oligolas sir,

Your code is helpful for me. But If I want to concatenate by group like 'Sex' variable, what lines I need to add or what will be the code? Can you please help me on that? I have attached an image of my result but problem it is not capturing more than 32767 character. My code is as below. 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;

[Oligolas]

@benhaz 

Hi,

try this:

%MACRO doit;
   %macro t;%mend t;

   %let maxVarLength=20;

   proc sort data=sashelp.class(rename=(name=original_Variable sex=doc_Number)) out=have; by doc_Number original_Variable; run;

   *Determine variables that can be grouped together and still do not to exceed the maxVarLength;
   DATA _tempLen;
      length len 8;
      set have;
      by doc_Number;
      retain len 0 varcount 1;
      *Calculate length of concatenated field; 
      len=len+length(original_Variable);
      *Count space between variables;
      if len ne length(original_Variable) then len=len+1;

      if first.doc_Number then do;
         varcount=1;
         len=length(original_Variable);
         frst=1;
      end;
      else if len>&maxVarLength. then do;
         len=length(original_Variable);
         varCount=varCount+1;
         frst=1;
      end;
   RUN;

   *Determine how much concatenated fields are needed;
   PROC SQL noprint;
      select max(varCount) into:nvars TRIMMED
      from _tempLen
      ;
   QUIT;

   *Concatenate the original variable;
   DATA want;
      %do i=1 %to &nvars.;
         length concatenated_Field&i. $&maxVarLength.;
         retain concatenated_Field&i. '';
      %end;
      set _tempLen;
      by doc_Number;

      array vars(*) concatenated_Field1-concatenated_Field&nvars.;
      do i=varCount to varCount;
         if frst then vars(i)='';
         vars(i)=catx(' ',vars(i),strip(original_Variable)); ;
      end;

      drop len varCount frst i;
   RUN;

   PROC DATASETS lib=work nolist; delete _tempLen; RUN;QUIT;
%MEND doit;
%doit;

[rajdeep]

,case when STATUS_CODE LIKE '%VP%' then 
     SUBSTR (STATUS_CODE , INDEX ( STATUS_CODE , 'VP'),2) 
    else '' end length=2 AS VP_STATUS 
  
  
  ,case when STATUS_CODE LIKE '%VP%' then 
     SUBSTR (REASON_CODE , INDEX ( STATUS_CODE , 'VP'),2) 
    else '' end length=2 AS VP_REASON
  
  
  ,case when STATUS_CODE LIKE '%VP%' then
    input(SUBSTR (STATUS_DATE , (INDEX ( STATUS_CODE , 'VP') * 3)-2  ,4) || '-' 
 || 
     SUBSTR (STATUS_DATE , (INDEX ( STATUS_CODE , 'VP') * 3)+2  ,2) || '-' || 
     SUBSTR (STATUS_DATE , (INDEX ( STATUS_CODE , 'VP') * 3)+4  ,2) ,yymmdd10.) 
    else . end format date9.  AS VP_DATE

Suppose like in the above example there are more than 100 "case when" statements written line by line in a .sas file. So if I read by infile statement it will read without hassel as all the lines of code are less than 32767 characters, but when I will append it into a single line then I can get the complete query at one line and it can be more than 32767 characters. I think it will be easy for me to pull any one case when statement from the bunch of case when statements.

case when STATUS_CODE LIKE '%VP%' then SUBSTR (STATUS_CODE , INDEX ( STATUS_CODE , 'VP'),2) else '' end length=2 AS VP_STATUS ,case when STATUS_CODE LIKE '%VP%' then SUBSTR (REASON_CODE , INDEX ( STATUS_CODE , 'VP'),2) else '' end length=2 AS VP_REASON

 Now if I get the query like the above appended manner then I think if I want to retrieve only the below query then I can easily retrieve that with help sas string functions with some postional parameters. like Find or Findw funtions.

case when STATUS_CODE LIKE '%VP%' then SUBSTR (STATUS_CODE , INDEX ( STATUS_CODE , 'VP'),2) else '' end length=2 AS VP_STATUS

  So, I just wanted to know, how to store the remaining caharacters from the point it will exceed 32767 characters.

Hope this much of information will help.

[Patrick]

@rajdeep

SQL's exceeding 32KB of code. That sounds terrible to maintain.

32KB is the limit for a SAS character variable and there is no way around it.

You can either store your string in multiple character variables in a single observation or in multiple observations using some logic to determine when you have to switch over.

You could for example for every new input variable first check how much you've stored already in the current variable (using a length statement) and how long your new source string is - if there is no further space left then store the string in the next variable/next observation.

If this is simply about implementing some sort of a text search function out of SAS then consider to use OS text search functions instead which you can call out of SAS using a PIPE - and then only store the wanted result in SAS. UNIX/Linux for example provides very powerful commands for such tasks.

[Oligolas]

Hi,

what would you do with all this SQL splitted into variables? 

Can't you save it to a file?

data _Null_;
   file "C:\Users\&sysuserid.\Desktop\mySQLCode.txt";
   set sashelp.class;
   put name;
run;