topic SAS Error: Subquery evaluated to more than one row in SAS Data Management

SAS Error: Subquery evaluated to more than one row

AshJuri — Thu, 19 Mar 2020 20:16:49 GMT

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.

Re: SAS Error: Subquery evaluated to more than one row

Quentin — Thu, 19 Mar 2020 20:27:38 GMT

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.

Re: SAS Error: Subquery evaluated to more than one row

yabwon — Thu, 19 Mar 2020 20:35:08 GMT

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

Re: SAS Error: Subquery evaluated to more than one row

AshJuri — Thu, 19 Mar 2020 20:47:28 GMT

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

Re: SAS Error: Subquery evaluated to more than one row

Reeza — Thu, 19 Mar 2020 20:50:16 GMT

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.

Re: SAS Error: Subquery evaluated to more than one row

Tom — Thu, 19 Mar 2020 20:53:06 GMT

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

Re: SAS Error: Subquery evaluated to more than one row

Quentin — Thu, 19 Mar 2020 20:54:05 GMT

>From the code you posted?

Re: SAS Error: Subquery evaluated to more than one row

Tom — Thu, 19 Mar 2020 20:57:31 GMT

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?)

Re: SAS Error: Subquery evaluated to more than one row

Reeza — Thu, 19 Mar 2020 21:47:47 GMT

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;