09-01-2017 03:02 AM
data D2; length cat $32690; do until (last.l); set D1; by l notsorted; cat=catx(' ',cat,strip(sas_code)); end; run;
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.
09-01-2017 03:50 AM
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.
09-01-2017 04:37 AM - edited 09-01-2017 05:16 AM
I'm not sure that I understand what kind of data you are dealing with.
Quoting from your post:
"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=';'.
09-01-2017 05:39 AM
Yes My input is SAS code itself. The infile statement will read line by line & we need data in concatenated manner.
09-01-2017 07:59 AM
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;
- Cheers -
09-01-2017 08:26 AM
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
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.
09-04-2017 06:23 AM
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:
then a new variable is created. (according to your first post, you want 32690 here)
You have the code give it a try.
- Cheers -
09-02-2017 05:25 PM
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.
09-01-2017 07:59 AM
,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.
09-02-2017 09:33 PM - edited 09-02-2017 09:35 PM
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.
09-05-2017 04:33 AM
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 -
09-05-2017 05:56 AM
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.
09-05-2017 11:31 AM
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 -