topic Re: SAS proc query in SAS Procedures

SAS proc query

scb — Wed, 26 Jun 2019 09:05:31 GMT

I got this error message "Subquery evaluated to more than one row" in the below codes. Anyone can help? Thanks.

data d1;
input id $ name $;
datalines;
110 Miller
111 Bush
112 Elliot
113 Ryan
;
run;

data d2;
Format DATE1 DATE9.;
input id $ date1 DATE9.;
datalines;
110 01JAN2018
111 01FEB2018
112 01MAR2018
114 01APR2018
;
run;

data d3;
Format DATE2 DATE9.;
input id $ date2 DATE9.;
datalines;
110 01JAN2017
111 01FEB2019
112 01MAR2016
113 01APR2019
;
run;

PROC SQL;
CREATE TABLE WANT AS SELECT DISTINCT
* FROM D2 WHERE ID IN (SELECT ID FROM D1)
AND DATE1 < (SELECT DATE2 FROM D3);
QUIT;

Re: SAS proc query

Kurt_Bremser — Wed, 26 Jun 2019 09:14:43 GMT

SELECT DATE2 FROM D3

will result in a list with 4 values, but you may only have one value where you use that subquery. You need to add some kind of summary action to condense D3 into one row.

Re: SAS proc query

Kurt_Bremser — Wed, 26 Jun 2019 09:17:53 GMT

I guess you wanted to restrict it to corresponding id's:

select date2 from d3 where d3.id = d2.id