gpt4 book ai didi

sql - 在 SAS SQL 中使用宏

转载 作者:行者123 更新时间:2023-12-04 09:47:56 25 4
gpt4 key购买 nike

我想知道我是否可以将一些代码合并到一个 PROC SQL 语句中,而不是几个背靠背的 SQL 语句。

我正在使用宏来计算参与者在同意研究之日的年龄,类似于提供的内容 herehere .我尝试对宏的功能进行故障排除,并使用 %sum 宏(效果很好...)计算了婴儿出生时的总重量(以盎司为单位)。但是,在尝试计算年龄时,宏不起作用。

但是, 如果我在新的 SQL 语句中使用宏,它可以正常工作。

下面的代码有效:

%macro months(somedate,birth);
intck('month',&birth,&somedate)
- (day(&somedate) < day(&birth))
%mend months;

%macro days(somedate,birth);
intck('day',&birth,&somedate)
- (day(&somedate) < day(&birth))
%mend days;

%macro sum(part1, part2);
&part1*16 + &part2
%mend sum;


********** bringing in data from outside tables ;
proc sql;
create table demos as
select x.*, infcondt2 as c_dt,
y.*, datepart(visitdt) as v_dt format date9. ,
datepart(birthdt) as b_dt format date9. ,
birthweightlbs as lbs,
birthweightoz as oz,

lbs*16 + oz as tot_oz,
%sum(lbs,oz) as tot_oz_m

from enrolled as x,
demographics as y

where x.center = y.center and x.id = y.id ;
quit;

********** calculating age in months and in days ;
proc sql;
create table demos2 as
select * ,
%months(c_dt, b_dt) as age_m ,
%days(c_dt, b_dt) as age_d
from demos;
quit;


********** creating age groupings by months: 0-3 and 3-6 ;
proc sql;
create table demos3 as
select * ,
case
when age_m le 3 then 1
when age_m le 6 and age_m gt 3 then 2
else 3
end as age_interval
from demos2;
quit;

有什么办法可以把它合并成一个语句吗?像这样的东西:

proc sql;
create table demos as
select x.*, infcondt2 as c_dt,
y.*, datepart(visitdt) as v_dt format date9. ,
datepart(birthdt) as b_dt format date9. ,
birthweightlbs as lbs,
birthweightoz as oz,

lbs*16 + oz as tot_oz,
%sum(lbs,oz) as tot_oz_m,
%months(c_dt, b_dt) as age_m,
%days(c_dt, b_dt) as age_d,
case
when age_m le 3 then 1
when age_m le 6 and age_m gt 3 then 2
else 3
end as age_interval

from enrolled as x,
demographics as y

where x.center = y.center and x.id = y.id ;
quit;

最佳答案

如果要使用以前在同一 SQL 语句中创建的字段,则需要使用 calculated 关键字。即:

proc sql;
select age*2 as double_Age, calculated double_age/2 as normal_age from sashelp.class;
quit;

只有在进行实际计算时才需要计算 - 即,对于 b_Dt。 c_dt只是infcondt2的重命名,所以c_dt(或infcondt2)可以互换使用,不能使用CALCUALTED。

data test;
input dt1 :date9. dt2 :date9.;
datalines;
01JAN2010 01FEB2011
01DEC2011 03FEB2012
;;;;
run;

%macro months(somedate,birth);
intck('month',&birth,&somedate) - (day(&somedate) < day(&birth))
%mend months;

proc sql;
create table test2 as
select dt1 -1 as b_dt, dt2 as c_dt, %months(calculated b_dt, c_dt) as third
from test;
quit;

就是说,如果您有 9.2 或更早的版本,您不再需要调整日期 - 查看 INTCK 的文档。有一个可选的参数(在 9.3 中它被称为 METHOD,我认为 9.2 称之为不同的东西)允许你强制它使用连续的月份概念而不是离散的概念(计算第一个月是离散的,例如,以前的默认值)。

此外,我不明白 DAY 宏的意义 - DAY 不仅是整数(因此您可以使用正常减法将两个数字相减),但为什么要像 %month 那样减去日/日?正如我刚刚讨论的那样,这是为了纠正每月的部分时间,并且几天都不需要(它会在 somedate < birth 的地方产生错误的答案)。

正确代码示例:

%macro months(somedate,birth);
intck('month',&birth,&somedate)
- (day(&somedate) < day(&birth))
%mend months;

%macro days(somedate,birth);
intck('day',&birth,&somedate)
- (day(&somedate) < day(&birth))
%mend days;

%macro sum(part1, part2);
&part1*16 + &part2
%mend sum;

data enrolled;
input
id infcondt2 :date9.
;
datalines;
1 01JAN2011
2 02JAN2011
3 03MAR2011
;;;;
run;
data demographics;
input
id
birthweightlbs
birthweightoz
birthdt :datetime17.
;
datalines;
1 8 14 04MAR2011:15:13:14
2 7 13 05MAR2011:15:13:14
3 6 15 06MAR2011:15:13:14
;;;;
run;


proc sql;
create table demos as
select x.*, infcondt2 as c_dt,
y.*, datepart(birthdt) as b_dt format date9. ,
birthweightlbs as lbs,
birthweightoz as oz,
lbs*16 + oz as tot_oz,
%sum(lbs,oz) as tot_oz_m,
%months(c_dt, calculated b_dt) as age_m,
c_dt - calculated b_dt as age_d, /* %days is almost certainly wrong here */
case
when calculated age_m le 3 then 1
when 3 le calculated age_m le 6 then 2
else 3
end as age_interval

from enrolled as x,
demographics as y

where x.id = y.id ;
quit;

关于sql - 在 SAS SQL 中使用宏,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14200221/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com