DATA Step, Macro, Functions and more

ERROR: Subquery evaluated to more than one row.

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

ERROR: Subquery evaluated to more than one row.

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

as

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 


Accepted Solutions
Solution
‎10-23-2014 09:39 AM
Super User
Posts: 10,028

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

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

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

What are you trying to do based on this query?

Super User
Super User
Posts: 7,050

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

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'

;

Contributor
Posts: 71

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

Sorry for late reply.

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

Super User
Super User
Posts: 7,050

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

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;

Super User
Posts: 10,028

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

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

Contributor
Posts: 71

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

,

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.

Super User
Posts: 10,028

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

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  .

Contributor
Posts: 71

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

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.

Solution
‎10-23-2014 09:39 AM
Super User
Posts: 10,028

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

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

Contributor
Posts: 71

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 2675 views
  • 7 likes
  • 4 in conversation