在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.

使用此ucla关于proc排序的页面上的示例 ，您的示例将使用稍微修改的输入数据即可正常工作:

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广到长

数据步长到宽
使用数据步骤进行大量操作

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