topic Re: update table from another table in sas in SAS Enterprise Guide

update table from another table in sas

Daily1 — Wed, 01 Feb 2023 17:34:13 GMT

update sample1 from another sample2 in sas

match id and code then exp update

example.363823 and 52 matched then put 5.

i want this type of query ..Pls help

sample data

data sample1;
  input ID 1-6 code 7-9 exp 10-11;
  datalines;
363823	52	0
363823	53	0
293556	50	0
293556	51	0
293556	52	0
293556	53	0
;

data sample2;
  input ID 1-6 code 7-9 exp 10-11;
  datalines;
363823	52	5
363823	53	8
293556	50	9
;

I want this type of output

Re: update table from another table in sas

yabwon — Wed, 01 Feb 2023 18:17:16 GMT

Looks like you are doing a homework.

step 1) use Proc Sort and sort both datasets by ID and Code.

step 2) use Merge statement to combine both data sets by ID and Code, remember to use them in the Merge statement in order "sample1 sample2" so the values of exp variable form the second one will overwrite values of exp from the first one (only in case when there is a match by ID and Code of course)

try to do it yourself, in case of problems - ask

Bart

Re: update table from another table in sas

Daily1 — Wed, 01 Feb 2023 18:42:14 GMT

proc sql;
update sample1 set exp= select n.exp from sample2 as n 
where n.id=sample1.id and n.code =sample1.code;
quit;

Please Help

Re: update table from another table in sas

Daily1 — Wed, 01 Feb 2023 18:50:09 GMT

Sql Code

Result

this code not working on sas eg ...please help

Re: update table from another table in sas

Kurt_Bremser — Wed, 01 Feb 2023 19:14:27 GMT

Maxim 2: Read the Log.

If that does not give you a clue, post it here.

Re: update table from another table in sas

Daily1 — Wed, 01 Feb 2023 19:16:31 GMT

proc sql;
update sample1 set exp= (select n.exp from sample2 as n )
where n.id=sample1.id and n.code =sample1.code;
quit;

log
proc sql;
29 update sample1 set exp= (select n.exp from sample2 as n )
30 where n.id=sample1.id and n.code =sample1.code;
ERROR: Unresolved reference to table/correlation name n.
ERROR: Unresolved reference to table/correlation name n.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
31 quit;

Re: update table from another table in sas

Kurt_Bremser — Wed, 01 Feb 2023 19:20:22 GMT

The WHERE clause must be part of the sub-select.

Re: update table from another table in sas

Tom — Wed, 01 Feb 2023 19:23:25 GMT

Why not just skip the SQL and use normal SAS code?

The UPDATE statement is for applying transactions to existing datasets.

data sample1;
  input ID code exp ;
datalines;
293556	50	0
293556	51	0
293556	52	0
293556	53	0
363823	52	0
363823	53	0
;

data sample2;
  input ID code exp ;
datalines;
293556	50	9
363823	52	5
363823	53	8
;

/*
proc sort data=sample1; by id code; run;
proc sort data=sample2; by id code; run;
*/

data want;
  update sample1 sample2;
  by id code ;
run;

Re: update table from another table in sas

Daily1 — Wed, 01 Feb 2023 19:24:06 GMT

proc sql;
update sample1 set exp= (select n.exp from sample2 n )
where n.id=sample1.id and n.code =sample1.code;
quit;

Please correct query

Re: update table from another table in sas

Kurt_Bremser — Wed, 01 Feb 2023 19:28:47 GMT

I already gave you the answer.

Re: update table from another table in sas

Tom — Wed, 01 Feb 2023 19:38:04 GMT

That photograph does not appear to be proper SQL syntax for how the UPDATE statement works.

https://en.wikipedia.org/wiki/Update_(SQL)

The UPDATE statement has the following form:

UPDATE table_name SET column_name = value [, column_name = value ...] [WHERE condition]

Perhaps it is some extension provided by some other database language you have used in the past?

Re: update table from another table in sas

Tom — Wed, 01 Feb 2023 19:42:43 GMT

@Daily1 wrote:

proc sql;
update sample1 set exp= select n.exp from sample2 as n 
where n.id=sample1.id and n.code =sample1.code;
quit;

Please Help

Did you mean

update sample1 
set exp= 
 (select n.exp from sample2 n 
  where n.id=sample1.id and n.code =sample1.code
 )
where exists (select n.exp from sample2 n 
  where n.id=sample1.id and n.code =sample1.code
 )
;