SAS Error: Subquery evaluated to more than one row

[AshJuri]

I am getting the message "ERROR: Subquery evaluated to more than one row. I have posted the working code below. I would like to know how this error can be resolved while still having the program run as intended

data have;
input Subject Type :$12. Date &:anydtdte. Procedure :$12. Measurement;
format date yymmdd10.;
datalines;

500   Initial    15 AUG 2017      Invasive     20 
500   Initial    18 SEPT 2018     Surface      35 
500   Followup   12 SEPT 2018     Invasive     54 
428   Followup    2 JUL 2019      Outer        29 
765   Seventh     3 JUL 2018      Other        13 
500   Followup    6 NOV 2018      Surface      98 
428   Initial     23 FEB 2018     Outer        10 
765   Initial     20 AUG 2019     Other        19 
610   Third       21 AUG 2018     Invasive     66 
610   Initial     27 Mar 2018     Invasive     17 
999   Dummy       17 mar 2020     Some          1
999   Dummy       18 mar 2020     Some          2
999   Dummy       19 mar 2020     Some          3
;

proc sql;
create table want as
select *,
    (select max(measurement) 
     from have 
     where subject=a.subject and type=a.type and procedure=a.procedure 
     having date = max(date)) / min(measurement) as ratio
from have as a
group by subject, type, procedure
order by subject, date;
quit;

. Thank you in advance.

[Quentin]

I don't get that error message.  I do get the note:

NOTE: The query requires remerging summary statistics back with the original data.

Please describe your intended result, and how it differs from the result you are getting.

[yabwon · Accepted solution]

How about moving subquery from SELECT to FROM?

proc sql;
create table want2 as
select a.*,
h.H_max / min(a.measurement) as ratio
from have as a,
    (select h.subject, h.type, h.procedure, max(h.measurement) as H_max
     from have as h 
     group by h.subject, h.type, h.procedure 
     having h.date = max(h.date)) as h
where h.subject=a.subject and h.type=a.type and h.procedure=a.procedure
group by a.subject, a.type, a.procedure
order by a.subject, a.date;
quit;

[edit] BTW. I didn't get any error too.

All the best

Bart

[AshJuri]

Hi, thank you for your response. I am getting an error stating "Expression using division (/) requires numeric types."

[Quentin]

>From the code you posted?

[Reeza]

Is your data set large? Would you be open to using a more 'basic' approach that used a couple of steps that were simpler or one complex solution in a single proc sql/data step? Not sure if you're trying to gain efficiency here or trying to get a task done.

[Tom]

Hard to tell without understanding what was intended. What output do you expect from that input?

[Tom]

You have two references to DATE and two to MEASUREMENT without any alias. Which version of those variables did you intend to reference? A.DATE or B.DATE? ( or one of each?)

[Reeza]

Ok, based on posts here and SO this is my suggestion - use a back to basic approach and data steps will be better for that. 

First calculate the summary stats you need in the data step and then merge the statistics in and calculate your remaining measures. 

I'm not sure if  order matters but I assume that follow ups should be after initial so not sure if your dates in the example data are reflective of your actual data. And it still doesn't read in correctly - if you run the first code it'll error our. I think I posted that note on SO. 

Here's an example of how that could work, finding the first, last, min and maximum for each subject. Using different BY groups would allow you change this by subject/date or other various combinations. It's also a single pass across the data which is more efficient than your SQL IMO.

data have;
input Subject $ Type :$12. Date &:anydtdte. Procedure :$12. Measurement;
format date yymmdd10.;
datalines;
500   Initial    15 AUG 2017      Invasive     20 
500   Initial    18 SEPT 2018     Surface      35 
500   Followup   12 SEPT 2018     Invasive     54 
428   Followup    2 JUL 2019      Outer        29 
765   Seventh     3 JUL 2018      Other        13 
500   Followup    6 NOV 2018      Surface      98 
428   Initial     23 FEB 2018     Outer        10 
765   Initial     20 AUG 2019     Other        19 
610   Third       21 AUG 2018     Invasive     66 
610   Initial     27 Mar 2018     Invasive     17 
999   Dummy       17 mar 2020     Some          1
999   Dummy       18 mar 2020     Some          2
999   Dummy       19 mar 2020     Some          3
;

proc sort data=have;
by subject date;
run;

*add first, min, max, last;
data _metrics;
set have;
by subject date;
retain measure_first measure_last measure_max 0 measure_min 99999;

if first.date then measure_first = Measurement;
if last.date then measure_last = Measurement;
if measurement>measure_max then measure_max = Measurement;
if  measurement<measure_min then measure_min = Measurement;

if last.subject;

keep subject measure_:;
run;



data want;
merge have _metrics;
by subject;

/*insert remaining calculations here*/

run;