问题描述
假设我喜欢做类似下面的事情(为了更好的可读性，使用示例性的变量名):
Suppose I like to do something like the following (with exemplary variable names for better readability):
取一个参数
InParameter并将其与数据集MyData
返回变量 MyVar2 的所有值，用于过滤后的观察
return all values for the variable MyVar2 for the filtered observations
来自子程序/函数
我可以在 proc sql/datastep 中使用
that i can use inside proc sql/datastep
这是我到目前为止得到的(显然不起作用):
This is what I got so far (clearly not working):
proc fcmp outlib=work.funcs.MyFunction;
function MyFunction(InParameter $);
array MyArray ... ; /* Here: Create an array with something like SELECT MyVar2 FROM MyData WHERE MyVar1 = Inparameter */
return(MyArray{});
endsub;
;
quit;
options cmplib=work.funcs;
data MyOutput;
set Somedata;
if MyVar2 in MyFunction("H20") then output;
run;
简而言之:
- 可以从函数/子例程内部访问数据集中的数据吗?
- 函数/子程序可以返回数组吗?
感谢您的帮助！
推荐答案
我们创建了一个名为 %ds2list() 的实用程序宏，它将执行您想要的过程.它不使用数组语句，但可以达到相同的结果.
We created a utility macro called %ds2list() that will perform your desired process. It doesn't use an array statement but it achieves the same result.
该宏仅以列表格式从数据集中返回值.这是调用它的示例:
The macro simply returns values from a dataset in a list format. Here's an example of calling it:
%put %ds2list(iDs=sashelp.class, iField=name, iQuote=1);
这将返回:
'Alfred','Alice','Barbara','Carol','Henry','James','Jane','Janet','Jeffrey','John','Joyce','Judy','Louise','Mary','Philip','Robert','Ronald','Thomas','William'
%ds2list() 的默认行为是用逗号分隔返回的值，但它非常灵活.您可以将分隔符更改为您选择的值(或无分隔符)，您可以打开或关闭引号，或者将它们从单引号更改为双引号，并且您可以提供通常在 set 语句，例如 where=() 语句.
The default behavior for %ds2list() is to comma separate the returned values but it is very flexible. You can change the delimiter to a value of your choice (or no delimiter), you can turn the quotes on or off, or change them from single to double quotes, and you can provide any dataset options you would normally use on a set statement such as a where=() statement.
此外，由于宏是纯宏代码，因此您可以在 SAS 中的任何地方使用它.在任何你喜欢的过程/数据/宏中.当我们有大量想要返回的 ID 时，我们广泛使用它来调用 ODBC passthrough.
Additionally because the macro is pure macro code you can use this literally anywhere in SAS. In any proc/data/macro you like. We use it extensively for calls to ODBC passthrough when we have a large list of IDs we want to be returned.
这是一个如何使用它的示例.首先创建一个包含要与列表值进行比较的值的表:
Here's an example of how you could use it. First create a table that will contain values to compare against the list values:
data keep;
input name $;
datalines;
Alfred
Carol
Janet
run;
迭代我们要对照列表检查的值:
Iterate over the values we want to check against the list:
data want;
set keep;
if name in (%ds2list(iDs=sashelp.class, iField=name, iQuote=1, iDsOptions=where=(sex='F'))) then do;
output;
end;
run;
返回:
Obs name
=== =====
1 Carol
2 Janet
您可以看到 Alfred 被排除在结果之外，因为他被 where=() 子句过滤掉.
You can see Alfred was excluded from the result because he was filtered out by the where=() clause.
这是宏，我建议把它放在你的宏自动调用库中:
Here is the macro, I suggest putting it in your macro autocall library:
/***************************************************************************
** PROGRAM: MACRO.DS2LIST.SAS
**
** UTILITY PROGRAM THAT DETECTS RETURNS A LIST OF FIELD VALUES FROM A
** DATASET IN DELIMITED FORMAT.
**
** PARAMETERS:
** iDs : THE LIBNAME.DATASET NAME THAT YOU WANT TO CHECK.
** iField : THE FIELD THAT CONTAINS THE VALUES YOU WANT RETURNED IN A
** DELIMITED FORMAT.
** iDelimiter: DEFAULT IS A COMMA. THE DELIMITER TO USE FOR THE RETURNED LIST.
** iDsOptions: ANY STANDARD DATASET OPTIONS THAT YOU WOULD LIKE TO APPLY SUCH
** AS A WHERE STATEMENT.
** iQuote : (0=NO,1=YES). DEFAULT=0/NO. DETERMINES WHETHER THE RETURNED
** LIST IS QUOTED OR NOT.
** iQuoteChar: (SINGLE,DOUBLE) DEFAULT=SINGLE. SPECIFIES WHETHER SINGLE
** OR DOUBLE QUOTES ARE USED WHEN QUOTING THE RETURNED LIST
**
*****************************************************************************/
%macro ds2list(iDs=, iField=, iDsOptions=, iDelimiter=%str(,), iQuote=0, iQuoteChar=single);
%local dsid pos rc result cnt quotechar value;
%let result=;
%let cnt=0;
%if &iQuote %then %do;
%if "%upcase(&iQuoteChar)" eq "DOUBLE" %then %do;
%let quotechar = %nrstr(%");
%end;
%else %if "%upcase(&iQuoteChar)" eq "SINGLE" %then %do;
%let quotechar = %nrstr(%');
%end;
%else %do;
%let quotechar = %nrstr(%");
%put WARNING: MACRO.DS2LIST.SAS: PARAMETER IQUOTECHAR INCORRECT. DEFAULTED TO DOUBLE;
%end;
%end;
%else %do;
%let quotechar = ;
%end;
/*
** ENSURE ALL THE REQUIRED PARAMETERS WERE PASSED IN.
*/
%if "&iDs" ne "" and "&iField" ne "" %then %do;
%let dsid=%sysfunc(open(&iDs(&iDsOptions),i));
%if &dsid %then %do;
%let pos=%sysfunc(varnum(&dsid,&iField));
%if &pos %then %do;
%let rc=%sysfunc(fetch(&dsid));
%do %while (&rc eq 0);
%if "%sysfunc(vartype(&dsid,&pos))" = "C" %then %do;
%let value = %qsysfunc(getvarc(&dsid,&pos));
%if "%trim(&value)" ne "" %then %do;
%let value = %qtrim(&value);
%end;
%end;
%else %do;
%let value = %sysfunc(getvarn(&dsid,&pos));
%end;
/* WHITESPACE/CARRIAGE RETURNS REMOVED IN THE BELOW LINE */
/* TO ENSURE NO WHITESPACE IS RETURNED IN THE OUTPUT. */
%if &cnt ne 0 %then %do;%unquote(&iDelimiter)%end;%unquote("echar&value"echar.)
%let cnt = %eval(&cnt + 1);
%let rc = %sysfunc(fetch(&dsid));
%end;
%if &rc ne -1 %then %do;
%put WARNING: MACRO.DS2LIST.SAS: %sysfunc(sysmsg());
%end;
%end;
%else %do;
%put ERROR: MACRO.DS2LIST.SAS: FIELD &iField NOT FOUND IN DATASET %upcase(&iDs).;
%end;
%end;
%else %do;
%put ERROR: MACRO.DS2LIST.SAS: DATASET %upcase(&iDs) COULD NOT BE OPENED.;
%end;
%let rc=%sysfunc(close(&dsid));
%end;
%else %do;
%put ERROR: MACRO.DS2LIST.SAS: YOU MUST SPECIFY BOTH THE IDS AND IFIELD PARAMETERS TO CALL THIS MACRO.;
%end;
%mend;
这篇关于SAS:在函数/子例程中查找数据/返回数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！