topic Re: ERROR: Subquery evaluated to more than one row. in SAS Programming

ERROR: Subquery evaluated to more than one row.

pallis — Fri, 17 Oct 2014 15:34:02 GMT

data a1;

input sno name $ sal;

datalines;

1 jagan 300

2 Karti 400

3 Vankat 500

2 jagan 300

2 Karti 400

3 Vankat 500

;

run;

data a2;

input month$ sno role$;

datalines;

jan 1 Clerk

Feb 2 Cook

Nar 3 Account

;

run;

Looking for output:

Sno name sal jan feb

1 jagan 300 clerk cook

2 Karti 400 cook cook

3 venkat 500 accounts Accounts

Note: For every new month I must get a new column(one month lag).

Ex If I'm in month of april I must fill there roles for month march and column name as march.

Below is the query which i wrote but i'm getting error

proc sql;

create table a3

select a.*,

case when sno in (select sno from a2 where month='jan')

then

( select b.role from a1 as a left join a2 as b

on a.sno=b.sno)

else '#n/a' end as Jan

from a1 as a;

quit;

Please help out to solve this issue

Re: ERROR: Subquery evaluated to more than one row.

stat_sas — Fri, 17 Oct 2014 15:51:10 GMT

What are you trying to do based on this query?

Re: ERROR: Subquery evaluated to more than one row.

Tom — Fri, 17 Oct 2014 19:20:10 GMT

Looks to me like you are just doing a left join of the two tables.

create table a3 as

select a1.*

, coalesce(a2.role,'#n/a') as Jan

from a1 left join a2

on a1.sno=a2.sno

and a2.month = 'jan'

;

Re: ERROR: Subquery evaluated to more than one row.

pallis — Sat, 18 Oct 2014 05:54:13 GMT

Sorry for late reply.

Here my intention was to create month on month designation of the employee

Re: ERROR: Subquery evaluated to more than one row.

Ksharp — Sat, 18 Oct 2014 14:39:32 GMT

Since you didn't post what output you need . Try this one :

data a1;
input sno name $ sal;
datalines;
1 jagan 300
2 Karti   400
3 Vankat 500
2 jagan 300
2 Karti   400
3 Vankat 500
;
run;
 
 
data a2;
input month$ sno role$;
datalines;
jan 1 Clerk
Feb 2 Cook
Nar 3 Account
;
run;
 
proc sql;
create table a3
as
select a.*,
case when (select count(*) from a2 where month='jan' and sno=a.sno)     gt 0
          then
( select role from a2 where  sno=a.sno)
   else '#n/a' end as Jan
from a1 as a;
quit;

Xia Keshan

Re: ERROR: Subquery evaluated to more than one row.

Tom — Sat, 18 Oct 2014 15:11:00 GMT

Use PROC TRANSPOSE to convert A2.

proc transpose data=a2 out=month_role ;

by sno;

id month;

var role ;

run;

Then just merge by SNO.

data want ;

merge a1 month_role ;

by sno;

run;

Re: ERROR: Subquery evaluated to more than one row.

pallis — Sun, 19 Oct 2014 10:08:03 GMT

Can you explain me the process/execution steps how this case statements works

case when (select count(*) from a2 where month='jan' and sno=a.sno) gt 0

then

( select role from a2 where sno=a.sno)

from a1 as a.

In the Then Clause if there are multiple records then clause Will return more than one row isnt. Can you correct me if im wrong.

Re: ERROR: Subquery evaluated to more than one row.

Ksharp — Sun, 19 Oct 2014 10:36:22 GMT

when there are multiple obs selected in A2 with month='jan' and sno equal the current obs's sno

(select count(*) from a2 where month='jan' and sno=a.sno) gt 0

then set it as role of A2 when sno equal the current obs's sno .

Re: ERROR: Subquery evaluated to more than one row.

pallis — Wed, 22 Oct 2014 16:11:31 GMT

HI All,

Sorry for the late reply.

My data is 173424. When I apply the above logic the query is taking more than 30 min. I have 2000 records in my master. I created index. But still no use of it. Is there any other way to increase performance.

Can you suggest me.

Re: ERROR: Subquery evaluated to more than one row.

Ksharp — Thu, 23 Oct 2014 13:39:44 GMT

Oh . I would suggest to use data step , it was usually faster than sql in this scenario .

Re: ERROR: Subquery evaluated to more than one row.

pallis — Wed, 18 Mar 2015 15:03:35 GMT

I Transpose the data and mapped with by transactional data. It worked issue is solved. Thanks for your help