按前缀/起始字母对表变量进行排序

发布于 2024-10-09 15:26:06 字数 281 浏览 4 评论 0原文

这是针对 SAS 表的，因此 SQL 命令也可以使用。

我有一个包含 300 个变量的表；它们有 5 个不同的前缀，我想按这些前缀对它们进行排序。我希望它们按特定顺序排列（mtr 前缀在日期前缀之前），但按字母顺序排列也是可以接受的。

我认为 SQL 会有类似以下内容：

Select mtr*, date* from Table

或者

Select mtr%, date% from Table

原文

This is for a SAS table, so SQL commands would work, as well.

I have a table with 300 variables; they have 5 different prefixes, which I would like to sort them by. I want them in a particular order (mtr prefix before date prefix), but alphabetical would be acceptable.

I was thinking SQL would have something along the lines of:

Select mtr*, date* from Table

Select mtr%, date% from Table

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

想念有你 2024-10-16 15:26:06

正如 gbn 所说，您需要获取列名并动态构建一些 sql （或数据步骤代码）。

下面是一个解决方案，它从自动 SAS 视图中检索列名，该视图保存有关会话的元数据（按字母顺序排列）到单个宏变量中，然后您可以稍后在代码中使用该宏变量：（

proc sql noprint;
  select name into :orderedVarNames separated by ','
  from sashelp.vcolumn
  where libname='WORK' and memname='YOUR_TABLE_NAME'
  order by name
  ;
quit;

显然，您需要将带引号的值替换为表的正确库名称和表名称。）然后您可以在另一步骤中使用此宏变量，如下所示：

proc sql;
  select &orderedVarNames
  from YOUR_TABLE_NAME
  ;
quit;

此处，“&orderedVarNames”被解析为列名称列表。您可以通过将变量放入日志来检查变量中的内容，如下所示： %put &orderedVarNames;

还有其他方法可以实现您的想法，但这可能是最快且最有效的方法。适用于任何桌子。如果您打算在数据步骤中对变量列表使用此技术，请将分隔符更改为以“ ”分隔。

一旦掌握了这一点，您就可以通过生成多个宏变量并过滤从 sashelp.vcolumn 检索的内容来定制解决方案以获得所需的确切顺序。像这样的东西：

proc sql noprint;
  select name into :orderedMTRvars separated by ','
  from sashelp.vcolumn
  where libname='WORK' and memname='MYTABLE' and substr(name,1,3)='MTR'
  order by name
  ;
  select name into :orderedDATEvars separated by ','
  from sashelp.vcolumn
  where libname='WORK' and memname='MYTABLE' and substr(name,1,4)='DATE'
  order by name
  ;
  quit;

  proc sql;
    select &orderedMTRVars, &orderedDATEVars
    from MYTABLE
    ;
  quit;

As gbn says, you'll need to get the column names and dynamically build some sql (or data step code).

Here's a solution that retrieves the column names from an automatic SAS view that holds metadata about your session, ordered alphabetically, into a single macro variable which you can then use later in your code:

proc sql noprint;
  select name into :orderedVarNames separated by ','
  from sashelp.vcolumn
  where libname='WORK' and memname='YOUR_TABLE_NAME'
  order by name
  ;
quit;

(Obviously you'll need to replace the quoted values with the correct libname and table name for your table.) Then you can use this macro variable in another step, like this:

proc sql;
  select &orderedVarNames
  from YOUR_TABLE_NAME
  ;
quit;

Here, "&orderedVarNames" is resolved to the list of column names. You can check what is in the variable by putting it out to the log thus: %put &orderedVarNames;

There are other ways to do what you're thinking of, but this is probably the quickest and will work for any table. If you were going to use this technique for a variable list in a data step, change the separator to separated by ' '.

Once you've got the hang of this, you could then tailor the solution to get the exact order you want by generating more than one macro variable and filtering what you're retrieving from sashelp.vcolumn. Something like this:

proc sql noprint;
  select name into :orderedMTRvars separated by ','
  from sashelp.vcolumn
  where libname='WORK' and memname='MYTABLE' and substr(name,1,3)='MTR'
  order by name
  ;
  select name into :orderedDATEvars separated by ','
  from sashelp.vcolumn
  where libname='WORK' and memname='MYTABLE' and substr(name,1,4)='DATE'
  order by name
  ;
  quit;

  proc sql;
    select &orderedMTRVars, &orderedDATEVars
    from MYTABLE
    ;
  quit;

回复收藏 0 原文

~没有更多了~