sql - SAS 表字符串长度(限制)

Question

我正在创建一个 SAS 表，其中一个字段必须包含一个巨大的字符串。

以下是我的表(表名 = 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.
.       .           .
.       .           .
.       .           .

我必须得到一个与此类似的表(表名 = 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.
.       .           .
.       .           .
.       .           .

因此，在尝试使用 SQL 失败后，我想到了以下 SAS 代码(请注意我是 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;

SAS 日志生成此警告消息:

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.

后跟消息(针对我在此处发布的 SAS 数据步骤):

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

请注意，在我的示例数据表(MKPLOGS4)中，字段 DESCTEXT 的每行字符串最多可以包含 116 个字符，并且对于描述文本/记录 ID 的行数没有限制。

我得到的输出只有最后一行描述:

OBS   RNID   DESCTEXT
      ----   --------
1     123    String concatenation.
2     124    Are you sure this is not your jacket, okay then. Will give charity.
.     .      .
.     .      .
.     .      .

我有以下问题:

.我的代码有问题吗？. SAS 字符串连接有限制吗？我可以覆盖这个吗？如果是，请提供代码。

如果您有任何建议，请发布您的代码版本，我将不胜感激。这不是学校作业/家庭作业。

Answer 1

由于 SAS 将字符数据存储为空格填充的 固定 长度字符串，因此在数据集中存储大量文本通常不是一个好主意。但是，如果必须，则可以创建长度最多为 32767 个字符的字符类型变量。如果您不介意做一些额外的 I/O，这里有一个简单的方法。

   /* test data -- same id repeated over multiple observations i.e., in a "long-format" */
   data one;
     input rnid desctext & :$200.;
   cards;
   123 This is some text which is part of the record. I want this to appear
   123 concatenated kinda like concat_group() from MYSQL but for some reason
   123 SAS does not have such functionality. Now I am having trouble with
   123 String concatenation.
   124 Hi there old friend of mine, hope you are enjoying the weather
   124 Are you sure this is not your jacket, okay then. Will give charity.
   ;
   run;
   /* re-shape from the long to the wide-format. assumes that data are sorted by rnid. */
   proc transpose data=one out=two;
     by rnid;
     var desctext;
   run;
   /* concatenate col1, col2, ... vars into single desctext */
   data two;
     length rnid 8 desctext $1000;
     keep rnid desctext;
     set two;
     desctext = catx(' ', of col:);
   run;