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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.