BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jeka
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

7 REPLIES 7
Jeka
Obsidian | Level 7

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.

Jeka
Obsidian | Level 7

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.

Jeka
Obsidian | Level 7
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;
mkeintz
PROC Star

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;

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Jeka
Obsidian | Level 7
mkeintz thank you!

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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