如何实现“Last.或第一."使用 proc sql [英] How to achieve "Last. or First." using proc sql

问题描述

我正在努力实现最后".使用 Proc SQL 而不是数据步骤的功能.假设我有一个如下数据集:

I am trying to achieve the "last." functionality using Proc SQL instead of data step. Say I have a dataset like below:

Account_Id Dept Salary Emp_Status Projects Rating
111         123  7000  Perm       A        5
111         123  7000  Perm       B        4
111         123  7000  Perm       C        5
222         124  6000  Perm       A        5
333         125  7000  Perm       B        4
333         125  7000  Perm       C        5

我希望每个 account_id 在我的输出中只有一行.所以，我想要last.account_id.如何使用 proc sql 实现这一点.我在按 account_id 分组时尝试使用 max(monotnic()) 但没有用.有人可以帮忙吗.此外，由于某些标准项目限制，我无法使用或执行子查询.在proc sql中有没有其他方法可以做到这一点?

I want only one row in my output for each account_id. So, i want the last.account_id. How can I achieve this using proc sql. I tried using max(monotnic()) while grouping on account_id but did not work. Can someone please help. Also, I cannot use or do a sub-query due to some standard project limitations. Is there any other way of doing this in proc sql?

提前致谢！

推荐答案

假设您只关心输入数据集的行顺序而不关心值，以下似乎对您发布的示例数据执行了您想要的操作任何特定变量以确定按组内的顺序:

The following appears to do what you want for the sample data you have posted, assuming that you care only about the row order of your input dataset rather than the values of any particular variable to determine the order within by-groups:

data have;
input Account_Id Dept Salary Emp_Status $ Projects $ Rating;
cards;
111         123  7000  Perm       A        5
111         123  7000  Perm       B        4
111         123  7000  Perm       C        5
222         124  6000  Perm       A        5
333         125  7000  Perm       B        4
333         125  7000  Perm       C        5
;
run;

proc sql;
  create table want as
    select *, monotonic() as row_id from have
    group by account_id
    having row_id = max(row_id);
quit;

这似乎与你所说的你已经尝试过的非常相似，所以如果它不起作用，请提供一些重现问题的示例输入数据.

This seems quite similar to what you say you've already tried, so if it doesn't work, please provide some sample input data that reproduce the problem.

一般来说，我建议不要在生产代码中使用 monotonic()，因为它没有文档记录，并且可能在更复杂的查询中导致意外结果.使用 sql 时，您应该使用变量来定义行顺序.

In general I would advise against using monotonic() in production code as it is undocumented and can cause unexpected results in more complex queries. When working with sql you should use a variable to define your row order.

这篇关于如何实现“Last.或第一."使用 proc sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！