DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 26

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

 

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.

Super User
Super User
Posts: 8,287

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

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

 

Contributor
Posts: 26

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

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.

Trusted Advisor
Posts: 1,848

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

[ Edited ]

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

Contributor
Posts: 26

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

Hi,

 

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

 

Thanks

Trusted Advisor
Posts: 1,848

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

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

Are you looking for strings in different sas statemnets ?

Regular Contributor
Posts: 164

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

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;

splitString.png

________________________

- Cheers -

Contributor
Posts: 26

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

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.

Regular Contributor
Posts: 164

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

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.

 

________________________

- Cheers -

Contributor
Posts: 26

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

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.

Contributor
Posts: 26

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

 

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

Respected Advisor
Posts: 4,797

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

[ Edited ]

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

 

Regular Contributor
Posts: 164

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

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

- Cheers -

Contributor
Posts: 26

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

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

Regular Contributor
Posts: 164

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

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);
________________________

- Cheers -

Ask a Question
Discussion stats
  • 16 replies
  • 517 views
  • 0 likes
  • 5 in conversation