如何在 SAS 中按降序添加一个变量的多个组在 SAS 中添加行号? [英] How do you add a row Number in SAS by multiple groups with one variable in decending order?

问题描述

我在 SAS 中发现了这段代码，它模仿了 SQL Server 中的以下窗口函数:

I have discovered this code in SAS that mimics the following window function in SQL server:

ROW_NUMBER() OVER (PARTITION BY Var1,var2 ORDER BY var1, var2) 

=

data want;
set have
by  var1  var2;
if first.var1 AND first.var2 then n=1;
else n+1;
run;

她是个美女克拉克"……但是，如何模仿这种操作:

"She's a beaut' Clark"... but, How does one mimic this operation:

ROW_NUMBER() OVER (PARTITION BY Var1,var2 ORDER BY var1, var2 Desc)

我之前已经确定:

PROC SORT DATA=WORK.TEST
OUT=WORK.TEST;
BY var1 DECENDING var2  ;
RUN;

data WORK.want;
set WORK.Test;
by  var1 var2;
if first.var1 AND last.var2 then n=1;
else n+1;
run;

但这行不通.

错误:BY 变量未在数据集 WORK.TEST 上正确排序.

样本数据集:

data test;   
infile datalines dlm='#';
INPUT var1 var2;
datalines;
1#5 
2#4
1#3
1#6
1#9 
2#5 
2#2 
1#7
; 
run; 

我想我可以使一个变量临时为负，但我不想更改数据，我正在寻找更优雅的解决方案.

I was thinking I can make one variable temporary negative, but I don't want to change the data, I'm looking for a more elegant solution.

推荐答案

您必须告诉数据步骤以降序期望数据，如果这是您提供的数据.

You have to tell the data step to expect the data in descending order if that is what you are giving it.

您似乎也不太明白 FIRST 的逻辑.和最后.旗帜.如果它是 FIRST.VAR1，那么根据定义它就是 FIRST.VAR2.对该 VAR1 值的第一个观测值也是在该特定 VAR1 值内对 VAR2 的第一个值的第一个观测值.

You also don't seem to quite get the logic of the FIRST. and LAST. flags. If it is FIRST.VAR1 then by definition it is FIRST.VAR2. The first observation for this value of VAR1 is also the first observation for the first value of VAR2 within this specific value of VAR1.

您想对 VAR1 和 VAR2 的每个组合中的观测值进行编号吗?

Do you want to number the observations within each combination of VAR1 and VAR2?

data WORK.want;
  set WORK.Test;
  BY var1 DESCENDING var2  ;
  if first.var2 then n=1;
  else n+1;
run;

或者对 VAR1 中 VAR2 的不同值进行编号?

Or number the distinct values of VAR2 within VAR1?

data WORK.want;
  set WORK.Test;
  BY var1 DESCENDING var2  ;
  if first.var1 then n=0;
  if first.var2 then n+1;
run;

或者对 VAR2 和 VAR1 的不同组合进行编号?

Or number the distinct combinations of VAR2 and VAR1?

data WORK.want;
  set WORK.Test;
  BY var1 DESCENDING var2  ;
  if first.var2 then n+1;
run;

这篇关于如何在 SAS 中按降序添加一个变量的多个组在 SAS 中添加行号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！