DATA Step, Macro, Functions and more

find closest date using max() between in proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

find closest date using max() between in proc sql

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.


Accepted Solutions
Solution
‎01-16-2017 10:59 AM
Valued Guide
Posts: 797

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

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:

  1. The data are assumed to have one record per subject in fix_ex1_1.
  2. fix_ex1_1 is assumed to be sorted by subject, and visits are assumed to be sorted by subject/vddt
  3. The first "do until" scans all the VDDT's for a single subject to find the most recent qualifying date
  4. The second "do until" remerges the records that subject and outputs the qualified visit
  5. 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;

 

 

 

View solution in original post


All Replies
Super User
Posts: 6,936

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 14

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

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.

Super User
Posts: 6,936

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

Read my post again:

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 14

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

tried to use different modifications of group by, order by but it is not works... Smiley Frustrated

 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.

Occasional Contributor
Posts: 14

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

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;
Solution
‎01-16-2017 10:59 AM
Valued Guide
Posts: 797

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

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:

  1. The data are assumed to have one record per subject in fix_ex1_1.
  2. fix_ex1_1 is assumed to be sorted by subject, and visits are assumed to be sorted by subject/vddt
  3. The first "do until" scans all the VDDT's for a single subject to find the most recent qualifying date
  4. The second "do until" remerges the records that subject and outputs the qualified visit
  5. 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;

 

 

 

Occasional Contributor
Posts: 14

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

mkeintz thank you!
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 347 views
  • 1 like
  • 3 in conversation