在 SAS 中将长转换为宽 [英] Transpose long to wide in SAS

问题描述

我有一个非常大的数据集(1800 万个观测值)，我想通过基于一个变量的子集进行转置，并从这些子集中创建 900 个新变量.下面的示例代码和所需的输出格式:示例数据:

I have a very large data set (18 million observations) that I would like to transpose by subsetting based on one variable and creating 900 new variables out of those sub/ets. Example code and desired output format below: Example data:

data long1 ; 
  input famid year faminc ; 
cards ; 
var1 96 40000 
var1 97 40500 
var1 98 41000 
var2 96 45000 
var2 97 45400 
var2 98 45800 
var3 96 75000 
var3 97 76000 
var3 98 77000 
; 
run;

代码:

proc transpose data = data; by famid; var faminc; run;

所需的输出格式:

由于我的数据集的大小，我想知道我是否使用 do 循环来查找每个 var 的第一个和最后一个观察结果，然后迭代子集.有谁知道实现所需输出格式的 sql 或 proc?

Because of the size of my data set, I'm wondering if I have use a do loop to find first and last observations of each var and then iteratively subset. Does anyone know a sql or proc to achieve the desired output format?

推荐答案

我不确定你的子集是基于什么的，但如果它只是 id，它是相当简单的.

I'm not sure what your subsetting would be based on, but if it's only the id, it is rather straightforward.

使用 this ucla's page on proc sort 中的示例，您的示例可以使用稍微修改的输入数据正常工作:

Using the example from this ucla's page on proc sort, your example would work fine using slightly modified input data:

data long; 
  input id year faminc ; 
  datalines ; 
1 96 40000 
1 97 40500 
1 98 41000 
2 96 45000 
2 97 45400 
2 98 45800 
3 96 75000 
3 97 76000 
3 98 77000 
; 

proc sort data=long;
  by year;
run;

proc transpose data=long out=wide(drop=_name_) prefix=var; 
  by year; 
  var faminc; 
run;

结果

year var1  var2  var3 
96   40000 45000 75000 
97   40500 45400 76000 
98   41000 45800 77000 

<小时>

UCLA 的 SAS 帮助页面 在双向使用 proc transpose 方面比 SAS 自己的要清楚得多.这里有 4 个有价值的链接...

---

UCLA's SAS help pages are much clearer than SAS's own as regards to using proc transpose in both directions. Here are 4 valuable links...

Proc Transpose 从长到宽
Proc Transpose 从宽到长

Long to Wide with Data Step
Wide to Long with Data Step

这篇关于在 SAS 中将长转换为宽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！