topic Re: How to append a character string more than 32767 in one variable in SAS Programming

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

rajdeep — Fri, 01 Sep 2017 07:02:11 GMT

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.

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

Tom — Fri, 01 Sep 2017 07:06:03 GMT

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

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

rajdeep — Fri, 01 Sep 2017 07:50:32 GMT

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.

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

Shmuel — Fri, 01 Sep 2017 09:16:28 GMT

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=';'.

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

rajdeep — Fri, 01 Sep 2017 09:39:03 GMT

Hi,

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

Thanks

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

Shmuel — Fri, 01 Sep 2017 09:41:37 GMT

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

Are you looking for strings in different sas statemnets ?

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

Oligolas — Fri, 01 Sep 2017 11:59:35 GMT

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;

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

rajdeep — Fri, 01 Sep 2017 11:59:37 GMT

,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.

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

rajdeep — Fri, 01 Sep 2017 12:26:59 GMT

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

xuz

xyuz

xyz

wordl;

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.

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

rajdeep — Sat, 02 Sep 2017 21:25:49 GMT

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.

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

Patrick — Sun, 03 Sep 2017 01:35:33 GMT

@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.

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

Oligolas — Mon, 04 Sep 2017 10:23:39 GMT

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.

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

Oligolas — Tue, 05 Sep 2017 08:33:53 GMT

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;

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

rajdeep — Tue, 05 Sep 2017 09:56:54 GMT

No, I need it insdie a variable or a column, so that I can pull the relevant information from that inside SAS environment. Bcoz my input itself a SAS Code only. I am reading it like how we read the log files inside the SAS.

I'm just capturing the all the codes inside a variable which is been wriiten between proc and quit, it's appending line by line info and capturing fine for small codes, but for bigger proc sql queries it's cutting the string after 32767.

So, I just asked you earlier will your given code will capture the data between certain keywords like hello and there or proc or sql. or it will just append the data based on the length of the string value.(like len >50)

Anyways, I will test your code and will revert back. Apart from my requirement I just wanted to know for my knowledge that if there is a string more than 32767 characters then how to split the string and store it.

Thanks

Cheers......................

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

Oligolas — Tue, 05 Sep 2017 15:31:57 GMT

Well I'm not sure if I would really proceed this with SAS.

One could try to extract all the procedures from the log using a powershell RegEx script, depending of the exact requirements it may reach a much better performance.

But using SAS, this should work for you:

%MACRO splitIntoVars(inputDS=,outputDs=,VarToSplit=,VarLimit=);
   %macro t;%mend t;
   /*
      Concatenates the *VarToSplit* variable into cat* variables of *VarLimit* length
      Whenever a cat variable has reached the *VarLimit* length, a new cat* variable is created
   */
   PROC SQL noprint;
      select ceil(sum(length(&VarToSplit.))/&VarLimit.) into:nvars TRIMMED
      from &inputDS.
      ;
   QUIT;

   DATA &outputDs.;
      %do n=1 %to &nvars.;
         length cat&n. $&VarLimit.;
         retain cat&n. '';
      %end;
      set &inputDS.;
      retain o_limit &VarLimit. o_reached 0 o__n 1;
      array vars(*) cat1-cat&nvars.;

      o_Remaining=length(&VarToSplit.);
      o_from=1;
      o_to=min(o_limit-o_reached,length(&VarToSplit.));
      do while(o_Remaining>=1);
         o_text=substr(&VarToSplit.,o_from,o_to);
         %*Debug: put o__n= name= o_reached= o_remaining= o_from= o_to= o_text=;
         vars(o__n)=cats(' ',vars(o__n),substr(&VarToSplit.,o_from,o_to));   
         o_Remaining=o_Remaining-o_to;
         o_reached=o_reached+o_to;
         o_from=o_from+o_to;
         o_to=min(o_Remaining,o_limit);
         if o_reached eq o_limit then do;
            o__n=o__n+1;         
            o_reached=0;
         end;
      end;
      drop o_:;
   RUN;
%MEND splitIntoVars;
%splitIntoVars(inputDS=sashelp.class,outputDs=test,VarToSplit=name,VarLimit=50);

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

rajdeep — Wed, 06 Sep 2017 09:24:26 GMT

Thanks Oligoals for your effort. Let me tell you what I had done for this.


data D1;
set test2; 
/* cnt=length(sas); */
cnt1=length(sas_code);
   if 
   Findw(sas_code,'Proc') or Findw(sas_code,'PROC') or Findw(sas_code,'proc') or 
   scan(sas_code,1)='Data' or Scan(sas_code,1)='data' or scan(sas_code,1)='DATA' then 
   do;
   l+1;
   end;
   else do;
   l+0;
   end;

run;



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

So, in the D2 dataset it's appending the query, so when the query length is going beyond 32767 then it's trimming it. I would like to store the remaining in other variable in the D2 datastep itself.

NOTE: test2 is the sas code itself.

Is it possible in realtime?

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

Oligolas — Fri, 08 Sep 2017 08:57:50 GMT

Did you try the code I sent you?

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

benhaz — Thu, 28 May 2020 16:10:10 GMT

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;

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

Oligolas — Tue, 02 Jun 2020 19:48:28 GMT

@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;