topic Re: find closest date using max() between in proc sql in SAS Programming

find closest date using max() between in proc sql

Jeka — Mon, 26 Dec 2016 14:02:32 GMT

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.

Re: find closest date using max() between in proc sql

Kurt_Bremser — Mon, 26 Dec 2016 14:27:00 GMT

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.

Re: find closest date using max() between in proc sql

Jeka — Mon, 26 Dec 2016 14:35:56 GMT

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.

Re: find closest date using max() between in proc sql

Kurt_Bremser — Mon, 26 Dec 2016 14:42:46 GMT

Read my post again:

"Also remove the b. wherever you use that variable."

Re: find closest date using max() between in proc sql

Jeka — Mon, 26 Dec 2016 14:43:09 GMT

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.

Re: find closest date using max() between in proc sql

Jeka — Mon, 26 Dec 2016 14:47:57 GMT

Using 'having' in this code it works but gives the empty dataset =(
proc sql;
create table fix_ex1 as
select
a.treat,
a.subject,
a.date format datetime22.3,
a.exstdt format datetime22.3,
a.exrdi,
a.date,
a.rec_ds,
a.dsdecod,
max(vddt) as vdd_max format datetime22.3,
b.foldername,
b.folderseq,
b.vdyn
from fix_ex1_1 as a
left join visits as b on a.subject=b.subject
having calculated vdd_max between a.exstdt and a.date;
*group by a.subject,b.folderseq,a.treat,a.date,a.exstdt,a.exrdi,a.date,a.rec_ds,a.dsdecod;
quit;

Re: find closest date using max() between in proc sql

mkeintz — Mon, 26 Dec 2016 19:40:29 GMT

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:

The data are assumed to have one record per subject in fix_ex1_1.
fix_ex1_1 is assumed to be sorted by subject, and visits are assumed to be sorted by subject/vddt
The first "do until" scans all the VDDT's for a single subject to find the most recent qualifying date
The second "do until" remerges the records that subject and outputs the qualified visit
After the second "do until", the "if in1 and vdd_max=." statement outputs any subject with no qualifying visits - thereby replicating the left join.

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;

Re: find closest date using max() between in proc sql

Jeka — Mon, 16 Jan 2017 15:58:57 GMT

mkeintz thank you!