topic Re: how to update multiple row using single row value from another file in SAS Programming

how to update multiple row using single row value from another file

stataq — Sun, 10 Sep 2023 01:19:25 GMT

hello,

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

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;

```

Re: how to update multiple row using single row value from another file

Amir — Fri, 08 Sep 2023 22:02:13 GMT

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.

Re: how to update multiple row using single row value from another file

Patrick — Sat, 09 Sep 2023 05:08:33 GMT

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.

Re: how to update multiple row using single row value from another file

ballardw — Sat, 09 Sep 2023 05:09:45 GMT

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.

Re: how to update multiple row using single row value from another file

Ksharp — Sat, 09 Sep 2023 10:03:38 GMT

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;

Re: how to update multiple row using single row value from another file

Tom — Sat, 09 Sep 2023 19:02:44 GMT

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.

Re: how to update multiple row using single row value from another file

Ksharp — Sun, 10 Sep 2023 09:08:16 GMT

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 .