Hi,
I encountered the same issue when I tried to update using the syntax below, and got an error message:
ERROR: Subquery evaluated to more than one row.
Could someone look at my code and see if this is a syntax error and help me correct it?
proc sql;
update lgddata.lgd_data_auto new
set auto_dfrt_other =
(select dfrt_other
from lgd2014.lgd_data_auto
where new.proc_yymm_dt = proc_yymm_dt);
quit;
Thanks
It's not a syntax error, but a runtime problem because your subquery (apparently) yields more than one record, and the UPDATE assignment needs exactly one value to put in.
See what just this clause yields:
select dfrt_other
from lgd2014.lgd_data_auto
where new.proc_yymm_dt = proc_yymm_dt
It's not a syntax error, but a runtime problem because your subquery (apparently) yields more than one record, and the UPDATE assignment needs exactly one value to put in.
See what just this clause yields:
select dfrt_other
from lgd2014.lgd_data_auto
where new.proc_yymm_dt = proc_yymm_dt
Thanks Chris. When you say "subquery (apparently) yields more than one record", are you referring to the number of unique value (in this case dfrt_other)? I just did a check on the number of unique values by each date, it seems all dates have unique rate values:
proc sql;
select
proc_yymm_dt, count(distinct dfrt_other)
from lgd2014.lgd_data_auto
group by proc_yymm_dt
having count(distinct dfrt_other) > 1;
quit;
The above code yields to a blank result
As said in the error message, it is the number of rows that must be less than or equal to one. Rows with duplicate dfrt_other values are a problem too. Replace count(distinct dfrt_other) with count(*) to check the number of rows.
I suspect you are missing a condition in your where clause. Get the problematic dates with the query:
proc sql;
select a.proc_yymm_dt, count(*) as n
from
lgddata.lgd_data_auto as a inner join
lgd2014.lgd_data_auto as b on a.proc_yymm_dt = b.proc_yymm_dt
group by a.proc_yymm_dt
having count(*) > 1;
quit;
(untested)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.