SAS Studio – 从宏变量列表计数并筛选多个列

huangapple go评论70阅读模式
英文:

SAS Studio - Count and filter over multiple columns from a macro variable list

问题

我目前正在检查大量表格,寻找数据集之间的连接键(所有表格的连接键都有不同的名称)。我的当前代码检查存在多少记录以及有多少记录的键值为0。我的代码如下:

%let table = table1;
proc sql;
create table linking_keys as
select * 
from work.my_datasets
where Table_Name = "&table" 
and linking_key NE 'N';
quit;

proc sql;
select Column_Name
into :lnkkeycol separated by ','
from linking_keys;
quit;

%put &lnkkeycol;
proc sql;
create table lnk_key_only as
select count(*)
from library1.&table;
quit;

proc sql;
create table lnk_key_only_2 as
select count(&lnkkeycol)
from library1.&table
where &lnkkeycol = 0;
quit;

当表格中只有一个连接键时,这段代码可以正常运行。但当有多个连接键时,就会出错。我尝试使用宏变量进行分组,但以下代码并没有取得太多成功:

proc sql;
create table lnk_key_only_2 as
select count(&lnkkeycol)
from library1.&table
where &lnkkeycol = 0
group by &lnkkeycol;
quit;

我不太确定我哪里出错了。

英文:

I am currently checking a large number of tables for linking keys(all with different names) between datasets. My current code checks for how many records exist and for how many records have a 0 key value. My code looks like this:

%let table = table1;
proc sql;
create table linking_keys as
select * 
from work.my_datasets
where Table_Name = "&table" 
and linking_key NE 'N';
quit;

proc sql;
select Column_Name
into :lnkkeycol separated by ','
from linking_keys;
quit;

%put &lnkkeycol;
proc sql;
create table lnk_key_only as
select count(*)
from library1.&table;
quit;

proc sql;
create table lnk_key_only_2 as
select count(&lnkkeycol)
from library1.&table
where &lnkkeycol = 0;
quit;

This works fine when there is only 1 linking key in the table, but when there is multiple, it falls over. I have tried grouping by macro variables but not had much success with the below:

proc sql;
create table lnk_key_only_2 as
select count(&lnkkeycol)
from library1.&table
where &lnkkeycol = 0
group by &lnkkeycol;
quit;

I am not entirely sure where I am going wrong.

答案1

得分: 1

你需要单独指定每个链接键列。如果有多个链接键列,你将其传递给 count() 函数:

count(col1, col2, col3)

这对于 count() 函数是无效的。相反,创建一个单独的宏变量,只在其值为0时计算每个链接键列。

proc sql;
    select cats('sum( (', column_name, '=0) ) as count_', column_name)
    into :lnkkeycol separated by ','
    from linking_keys;
quit;

这将创建单独的 sum() 函数,仅计算 column_name = 0 的条件。(boolean conditon) 是 SAS 中的快捷方式,如果条件为真或假,它将创建一个1/0值。如果我们将其封装在一个 sum 函数中,sum( (boolean condition) ),我们只能求和1的数量。

proc sql;
    create table lnk_key_only_2 as
    select &lnkkeycol
    from library1.&table;
quit;

这翻译成:

proc sql;
    create table lnk_key_only_2 as
       select sum( (col1=0) ) as count_col1
            , sum( (col2=0) ) as count_col2
            , sum( (col3=0) ) as count_col3
    from library1.&table;
quit;
英文:

You need to specify each link key column separately. If you have multiple link key columns, you are passing this into the count() function:

count(col1, col2, col3)

Which is invalid for the count() function. Instead, create a separate macro variable that counts each link key col only when its value is 0.

proc sql;
    select cats('sum( (', column_name, '=0) ) as count_', column_name)
    into :lnkkeycol separated by ','
    from linking_keys;
quit;

This will create individual sum() functions that will only count the condition where column_name = 0. (boolean conditon) is a shortcut in SAS that creates a 1/0 value if the condition is true or false. If we encapsulate it in a sum function, sum( (boolean condition) ), we can sum only the 1's.

proc sql;
    create table lnk_key_only_2 as
    select &lnkkeycol
    from library1.&table;
quit;

This translates into:

proc sql;
    create table lnk_key_only_2 as
       select sum( (col1=0) ) as count_col1
            , sum( (col2=0) ) as count_col2
            , sum( (col3=0) ) as count_col3
    from library1.&table;
quit;

huangapple
  • 本文由 发表于 2023年5月11日 18:45:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76226766.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定