BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mkhan2010
Calcite | Level 5
Hi,

I am creating a SAS table in which one of the fields has to holds a huge string.

Following is my table (TABLE name = MKPLOGS4):

OBS______RNID________DESCTEXT
__________-------________-----------------
1_________123_________This is some text which is part of the record. I want this to appear
2_________123_________concatenated kinda like concat_group() from MYSQL but for some reason
3_________123_________SAS does not have such functionality. Now I am having trouble with
4_________123_________String concatenation.
5_________124_________Hi there old friend of mine, hope you are enjoying the weather
6_________124_________Are you sure this is not your jacket, okay then. Will give charity.
.
.
.

and I have to get a Table similar to this (table name = MKPLOGSA):

OBS____RNID_________DESCTEXT
________-------_________-----------------
1_______123__________This is some text which is part of the record. I want this to appear concatenated kinda like concat_group() from MYSQL but for some reason SAS does not have such functionality. Now I am having trouble with String concatenation.
2_______124__________Hi, there old friend of mine, hope you are enjoying the weather Are you sure this is not your jacket, okay then. Will give charity.
.
.
.

So, after trying unsuccessfully with SQL, I came up with the following SAS code (please note I am very new at SAS):

DATA MKPLOGSA (DROP = DTEMP DTEXT);
SET MKPLOGS4;
BY RNID;

RETAIN DTEXT;

IF FIRST.RNID THEN
DO;
DTEXT = DESCTEXT;
DELETE;
END;
ELSE IF LAST.RNID THEN
DO;
DTEMP = CATX(' ',DTEXT,DESCTEXT);
DESCTEXT = DTEMP;
END;
ELSE
DO;
DTEMP = CATX(' ',DTEXT,DESCTEXT);
DTEXT = DTEMP;

DELETE;
END;

The SAS log is producing this warning message:

WARNING: IN A CALL TO THE CATX FUNCTION, THE BUFFER ALLOCATED
FOR THE RESULT WAS NOT LONG ENOUGH TO CONTAIN THE CONCATENATION
OF ALL THE ARGUMENTS. THE CORRECT RESULT WOULD CONTAIN 229 CHARACTERS,
BUT THE ACTUAL RESULT MAY EITHER BE TRUNCATED TO 200 CHARACTER(S) OR
BE COMPLETELY BLANK, DEPENDING ON THE CALLING ENVIRONMENT. THE
FOLLOWING NOTE INDICATES THE LEFT-MOST ARGUMENT THAT CAUSED TRUNCATION.

Followed by the message (for the SAS data step I posted here):

NOTE: ARGUMENT 3 TO FUNCTION CATX AT LINE 100 COLUMN 15 IS INVALID.

Please note that in my sample data table (MKPLOGS4), each line of string for the field DESCTEXT can be upto 116 characters and there is no limit as to how many lines of description text/recordID.

The output I am getting has only the last line of description:

OBS___RNID____DESCTEXT
_______-------____-----------------
1______123_____String concatenation.
2______124_____Are you sure this is not your jacket, okay then. Will give charity.
.
.
.

I have the following questions:

. is there something wrong with my code?
. is there a limit to SAS string concatenation? Can I override this? If yes, please provide code.

If you have a suggestion, I would really appreciate if you can post your version of code.
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:
The key may be having an explicit LENGTH statement for your long text variable. I had this example that uses CATX, but without BY group processing that shows the use of the LENGTH statement set to $4000.

The length of a character variable is limited to 32,767 characters or bytes, as explained here:
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001336069.htm

cynthia
  

data singlevar;
 length sentence $100 ;
 infile datalines dsd dlm=',';
 input sentence $;
 len_sent = length(sentence);
 origord = _n_;
 return;
 datalines;
 "Twas brillig and the slithy toves"
 "Did gyre and gimble in the wabe"
 "All mimsy were the borogroves"
 "And the mome raths outgrabe"
 "Beware the Jabberwock, my son!"
 "The jaws that bite, the claws that catch!"
 "Beware the Jubjub bird, and shun"
 "The frumious Bandersnatch!"
 "He took his vorpal sword in hand:"
 "Long time the manxome foe he sought --"
 "So rested he by the Tumtum tree,"
 "And stood awhile in thought."
 "And, as in uffish thought he stood,"
 "The Jabberwock, with eyes of flame,"
 "Came whiffling through the tulgey wood,"
 "And burbled as it came!"
 "One, two! One, two! And through and through"
 "The vorpal blade went snicker-snack!"
 "He left it dead, and with its head"
 "He went galumphing back."
 "And, has thou slain the Jabberwock?"
 "Come to my arms, my beamish boy!"
 "O frabjous day! Callooh! Callay!"
 "He chortled in his joy."
 ;
 run;

 ods listing;;
 proc print data=singlevar;
 run;

 data bigvar(keep=longtext);
  length longtext $4000;
  retain longtext;
  set singlevar end=eof;
  longtext = catx(' ', longtext,sentence);
  if eof=1 then output;
 run;

 ods listing close;
 ods html file='c:\temp\show_bigvar.html' style=sasweb;
 proc print data=bigvar;
  title 'BIGVAR';
 run;
 ods _all_ close;
 title;


 

 

View solution in original post

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

Hi:
The key may be having an explicit LENGTH statement for your long text variable. I had this example that uses CATX, but without BY group processing that shows the use of the LENGTH statement set to $4000.

The length of a character variable is limited to 32,767 characters or bytes, as explained here:
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001336069.htm

cynthia
  

data singlevar;
 length sentence $100 ;
 infile datalines dsd dlm=',';
 input sentence $;
 len_sent = length(sentence);
 origord = _n_;
 return;
 datalines;
 "Twas brillig and the slithy toves"
 "Did gyre and gimble in the wabe"
 "All mimsy were the borogroves"
 "And the mome raths outgrabe"
 "Beware the Jabberwock, my son!"
 "The jaws that bite, the claws that catch!"
 "Beware the Jubjub bird, and shun"
 "The frumious Bandersnatch!"
 "He took his vorpal sword in hand:"
 "Long time the manxome foe he sought --"
 "So rested he by the Tumtum tree,"
 "And stood awhile in thought."
 "And, as in uffish thought he stood,"
 "The Jabberwock, with eyes of flame,"
 "Came whiffling through the tulgey wood,"
 "And burbled as it came!"
 "One, two! One, two! And through and through"
 "The vorpal blade went snicker-snack!"
 "He left it dead, and with its head"
 "He went galumphing back."
 "And, has thou slain the Jabberwock?"
 "Come to my arms, my beamish boy!"
 "O frabjous day! Callooh! Callay!"
 "He chortled in his joy."
 ;
 run;

 ods listing;;
 proc print data=singlevar;
 run;

 data bigvar(keep=longtext);
  length longtext $4000;
  retain longtext;
  set singlevar end=eof;
  longtext = catx(' ', longtext,sentence);
  if eof=1 then output;
 run;

 ods listing close;
 ods html file='c:\temp\show_bigvar.html' style=sasweb;
 proc print data=bigvar;
  title 'BIGVAR';
 run;
 ods _all_ close;
 title;


 

 

Ksharp
Super User
Hi.
Cynthia gives the right answer.

Refer to this:

http://support.sas.com/forums/thread.jspa?threadID=12254&tstart=0


Ksharp
mkhan2010
Calcite | Level 5
Thanks guys,

Just one last question:

After I do the concatenation - it works as in all the different lines of description text is now on same row HOWEVER, SAS log indicates that the SAS column width limit is set to 250 characters.

How can I override/edit this setting? I need to increase it to say 1000 characters.

Regards,
Ali.
Cynthia_sas
SAS Super FREQ
Hi:
Without seeing the EXACT error message, it is hard to guess; however, perhaps you have the LISTING destination still turned on???

Try using ODS LISTING CLOSE; before your PROC PRINT, because it could be the linesize in the LISTING window that's causing the issue. And the LISTING LINESIZE can ONLY be 256 -- but with ODS HTML, RTF or PDF, you do not have an issue with LINESIZE.

cynthia
[pre]
1092 ods listing;
1093 options ls=256;
1094 ods html file='c:\temp\show_bigvar.html' style=sasweb;
NOTE: Writing HTML Body file: c:\temp\show_bigvar.html
1095 proc print data=bigvar;
1096 title 'BIGVAR';
1097 run;

WARNING: Data too long for column "longtext"; truncated to 252 characters to fit.
NOTE: There were 1 observations read from the data set WORK.BIGVAR.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[/pre]
mkhan2010
Calcite | Level 5
Thanks Cynthia@sas,

I can confirm that the actual string attribute I use to hold the string value is storing the entire string (by using the LENGTH() function on the attribute and outputting it to SAS list).

Since I have to create excel report for this table, hopefully I won't have to be concerned about the max. string length limit to 256 characters (as was the case here).
Ksharp
Super User
Hi.
Function length() is not return the lenght of string stored. it returns the length of string without trailling blank .And Function lengthc() returns the lenght of string stored.

[pre]
data temp;
string='Whatever you want and ';

run;
data _null_;
set temp;
len = length(string);
lenc= lengthc(string);
put len= lenc=;
run;

[/pre]



Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 23200 views
  • 0 likes
  • 3 in conversation