SAS - 将行中的多个变量转置为列 [英] SAS - transpose multiple variables in rows to columns

问题描述

我正在尝试使用数组来完成以下操作.但是，我以前从未使用过，并且遇到了一些麻烦.

I'm trying to use an array to accomplish the following. However, I've never used one before and I'm having a bit of trouble.

基本上我想获取以下数据并为每个 acct、seq1、seq2 组合创建一行(观察):

Basically I want to take the following data and create one row(observation) for each acct, seq1, seq2 combination:

acct  seq1 seq2  la      ln
9999    1   10   20.01   100
9999    1   10   19.05   1
9999    2   11   26.77   100
9999    2   11   24.86   1
8888    3   20   38.43   218
8888    3   20   37.53   1

这就是我想要的结果.注意，为了空间起见，我只显示了 la1 到 la3 和 ln1 到 ln3.它实际上会转到 la7 和 ln7:

This is what I want to end up with. Note, I'm only showing la1 through la3 and ln1 through ln3 for the sake of space. It would actually to go to la7 and ln7:

acct  seq1  seq2     la1     la2    la3  ln1    ln2 ln3
9999    1   10      20.01   19.05   .    100     1   .
9999    2   11      26.77   24.86   .    100     1   .
8888    3   20      38.43   37.53   .    218     1   .

这是我迄今为止尝试过的代码.任何帮助将不胜感激:

Here is the code that I've attempted so far. Any assistance would be greatly appreciated:

data want;
set have; 
by acct seq1 seq2;
if first.seq2 then do;
array la_array {7} la1-la7;
array ln_array {7} ln1-ln7;
end;
do i = 1 to 7;
la_array(i)=la;
ln_array(i)=ln;
end;
run;

推荐答案

好吧...您必须记住，SAS 数组通常不是像大多数其他语言中那样的真实向量或内存变量数组.它们只是数据集变量的占位符.

Ok... you have to keep in mind that SAS arrays are tipically not real vectors or arrays of memory variables like in most other languages. They are just placeholders for dataset variables.

也就是说，ARRAY 语句只是用来定义数据集变量和速记数组引用之间的映射.把它放在你放置它的地方是没有用的，因为它不会初始化任何东西.

That said, the ARRAY statement is just there to define the mapping between the dataset variables and the shorthand array reference. It is useless to put it where you placed it as it does not initialize anything.

您应该考虑的另一件事是您需要累加这些值并写入一行.您不能在第一行执行此操作，因为您尚未读取所有数据.你必须使用最后一个.

Another thing you should consider is that you need to accumulate the values and write a single row. You cannot do that on the FIRST row as you have not read all the data yet. You have to use the LAST.

第三个问题是您定义的新变量在读取另一行时必须保留该值.SAS 通常会将所有不在输入数据集中的变量重置为 MISSING.为避免这种情况并在一组行上累积总和、最小值、最大值或其他值，您必须使用 RETAIN 语句.

A third issue is that the new variables you are defining must preserve the value when you read another row. SAS typically resets all variables that are not in the input dataset to MISSING. To avoid this and accumulate sums, min, max or other values on a group of rows, you must use the RETAIN statement.

最后...您可能想要删除您刚刚用于进行计算但在输出数据集中不需要的任何变量...在这种情况下为i".

Last... you may want to drop any variables you have just used to do your calculations, but are not desired in the ouput dataset... "i" in this case.

我认为这应该可以满足您的需求:

I think this should get you what you want:

DATA want (DROP=i);
  SET have; 
  BY acct seq1 seq2;
  /* define variables */
  ARRAY la_array {7} la1-la7;
  ARRAY ln_array {7} ln1-ln7;
  RETAIN i la1-la7 ln1-ln7;
  IF FIRST.seq2 then do;
    /* initialize group variables */
    DO i = 1 to 7;
      la_array(i)=.;
      ln_array(i)=.;
    END;
    /* initialize index variable */
    i = 1
  END;
  /* read input row values into array variables */
  la_array(i)=la;
  ln_array(i)=ln;
  i = i + 1;
  /* write output row if group is finished */
  IF LAST.seq2;
RUN;

很遗憾，我目前没有可用的 SAS 安装……您必须尝试一下，然后告诉我进展如何.

Unfortunately I do not have a SAS installation available at the moment... you'll have to try it and let me know how it goes.

这篇关于SAS - 将行中的多个变量转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！