BookmarkSubscribeRSS Feed
stataq
Quartz | Level 8

hello,

I have two dataset (master and update) like below and I would like to update `master`  using update.score if master.score=. .

 

stataq_0-1694206715767.png

How can I achieve expected outputs. 

I tried to do it like below and got "ERROR: Subquery evaluated to more than one row."

```

data output;
update master update;
by id;
run;

```

 

6 REPLIES 6
Amir
PROC Star

Hi,

 

A quick search for the error message ERROR: Subquery evaluated to more than one row. brings back links for proc SQL, as opposed to data step code like you shared.

 

Please post the log showing the code and any messages using the Insert Code icon "</>" when posting.

 

Also, is there any difference if you use a different data set name for your transaction data set, as update is a keyword and can cause its own error, e.g.:

 

 80         data update;
                 ______
                 56
 ERROR 56-185: UPDATE is not allowed in the DATA statement when option DATASTMTCHK=COREKEYWORDS.  Check for a missing semicolon in 
               the DATA statement, or use DATASTMTCHK=NONE.

 

 

 

Thanks & kind regards,

Amir.

Patrick
Opal | Level 21

The error message you share is from a SQL but the code you share is a data step. You will often get better answers faster if you spend a bit more time formulating the question. You will also get answers faster if you share sample data via working SAS data steps as done below and not just via screenshots. 

data work.master;
  infile datalines truncover dsd;
  input id score work $1.;
  datalines;
1,,Y
1,7,N
2,5,Y
3,,N
;

data work.update;
  infile datalines truncover dsd;
  input id score;
  datalines;
1,8
2,6
3,5
4,6
;

proc sql;
  update work.master as m
    set score=(select score from work.update where id=m.id)
    where missing(m.score)
    ;
  select * from work.master;
quit;

 ...and if you use two level table names then you will also never hit issues like below where you get an error because a table name is also a SAS keyword (update in this case).

 ERROR 56-185: UPDATE is not allowed in the DATA statement when option DATASTMTCHK=COREKEYWORDS.  Check for a missing semicolon in 
               the DATA statement, or use DATASTMTCHK=NONE.
ballardw
Super User

A slightly different approach;

proc sql;
   create table new as
   select a.id, a.work, coalesce(a.score,b.score) as score
   from work.master as a
        left join
        work.update as b
        on a.id=b.id
   ;
quit;

I tend to be very cautious about update in place until I am very sure of both data sets and the result so the above creates a new dataset.

The Coalesce, or for character values Coalescec, function returns the first of the non-missing values from the list of values. So this replaces the missing values in the set aliased as A with those in B.

Coalesce will take more than two parameters, so you could have multiple variables and/or a fixed value added to set the value if all of the variables are missing (no match in the update set for example).

 

Both this and @Patrick's solution will require that there only be one value of ID in the UPDATE set.

Ksharp
Super User
data work.master;
  infile datalines truncover dsd;
  input id score work $1.;
  datalines;
1,,Y
1,7,N
2,5,Y
3,,N
;

data work.update;
  infile datalines truncover dsd;
  input id score;
  datalines;
1,8
2,6
3,5
4,6
;

data want;
 update update master(in=inb);
 by id;
 if inb then output;
run;
Tom
Super User Tom
Super User

So this is reversing the order of the original and transactions datasets in the UPDATE statement can have useful applications.

 

But in this case it is probably not correct.  The problem will be when the original dataset has some values populated and some values missing then what the missing is replaced with is probably not what the original poster intended.

 

Consider the case where your ORIGINAL dataset looks like:

data work.original;
  input id score ;
datalines;
1 . 
1 7 
1 . 
;

And your transaction dataset has :

data work.transactions;
  input id score;
datalines;
1 8
;

And you apply your method of treating the transactions as the original and the original as the transcations:

data work.want ;
  update work.transactions work.original(in=in_original);
  by id;
  if in_original then output;
run;

The second missing value of SCORE is replaced with the preceding value,7, instead of the value from transactions datasets which is 8.

Ksharp
Super User
Tom,
You are right. My code is based on the data OP posted.
It is all depended on what OP look for.

For your special case, you could
PROC SORT data=master; by id score;run;
firstly before UPDATE .
Of course .it is all depended on what OP want .

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1166 views
  • 0 likes
  • 6 in conversation