英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论