Hi All,
Could someone to help me please.
I need to take closest visit date which lies between (start date) and (end date) of treatment taken. I tried to make it using sql but my skills is not so far as wanted. Here is piece of code:
proc sql;
create table fix_ex1 as
select
a.treat,
a.subject,
a.date,
a.exstdt,
a.exrdi,
a.date,
a.rec_ds,
a.dsdecod,
max(b.vddt) as b.vdd_max,
b.foldername,
b.folderseq,
b.vdyn
from fix_ex1_1 as a
left join visits as b on a.subject=b.subject
where calculated b.vdd_max between a.exstdt and a.date
order by a.subject,b.vdd_max,b.folderseq;
quit;
But error comes out:
max(b.vddt) as b.vdd_max,
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN,
LENGTH, TRANSCODE.
ERROR 76-322: Syntax error, statement will be ignored.
I think you need a subquery to filter out all visits not between start and end dates, and then get the maximum from that filtered subquery. Something like:
proc sql;
create table want as select * from
(select a.subject, a.treats a.date .... ,b.foldername, ..., b.vddt as vdd_max
from fix_ex_1 as a left join visits as b on a.subject=b.subject
where b.vddtt between a.exstdt and a.date)
group by subject
having vdd_max=max(vdd_max)
;
quit;
But I leave it to others far more expert in PROC SQL to confirm or improve.
But, in case you're interested, here is a DATA step solution.
Notes:
data want;
do until (last.subject);
merge fix_ex1_1 (keep=subject exstdt date) visits (keep=subject vddt);
by subject;
if exstdt<=vddt<=date then vdd_max=vddt;
end;
do until (last.subject);
merge fix_ex1_1 (in=in1) visits ;
by subject;
if vddt=vdd_max then output;
end;
if in1 and vdd_max=. then output;
run;
as b.vdd_max
remove the b.
Target variable names can't have a table alias prefix.
Also remove the b. wherever you use that variable.
Thank you for response . But it is not helps. Or i missed something else?
proc sql;
create table fix_ex1 as
select
a.treat,
a.subject,
a.date,
a.exstdt,
a.exrdi,
a.date,
a.rec_ds,
a.dsdecod,
max(vddt) as vdd_max,
b.foldername,
b.folderseq,
b.vdyn
from fix_ex1_1 as a
left join visits as b on a.subject=b.subject
where calculated vdd_max between a.exstdt and a.date
order by a.subject,b.vdd_max,b.folderseq;
ERROR: Column vdd_max could not be found in the table/view identified with the correlation name B.
ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.
Read my post again:
"Also remove the b. wherever you use that variable."
tried to use different modifications of group by, order by but it is not works...
proc sql;
create table fix_ex1 as
select
a.treat,
a.subject,
a.date,
a.exstdt,
a.exrdi,
a.date,
a.rec_ds,
a.dsdecod,
max(vddt) as vdd_max,
b.foldername,
b.folderseq,
b.vdyn
from fix_ex1_1 as a
left join visits as b on a.subject=b.subject
where calculated vdd_max between a.exstdt and a.date
group by a.subject,b.folderseq,a.treat,a.subject,a.date,a.exstdt,a.exrdi,a.date,a.rec_ds,a.dsdecod;
ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.
I think you need a subquery to filter out all visits not between start and end dates, and then get the maximum from that filtered subquery. Something like:
proc sql;
create table want as select * from
(select a.subject, a.treats a.date .... ,b.foldername, ..., b.vddt as vdd_max
from fix_ex_1 as a left join visits as b on a.subject=b.subject
where b.vddtt between a.exstdt and a.date)
group by subject
having vdd_max=max(vdd_max)
;
quit;
But I leave it to others far more expert in PROC SQL to confirm or improve.
But, in case you're interested, here is a DATA step solution.
Notes:
data want;
do until (last.subject);
merge fix_ex1_1 (keep=subject exstdt date) visits (keep=subject vddt);
by subject;
if exstdt<=vddt<=date then vdd_max=vddt;
end;
do until (last.subject);
merge fix_ex1_1 (in=in1) visits ;
by subject;
if vddt=vdd_max then output;
end;
if in1 and vdd_max=. then output;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.