update table from another table in sas

[Daily1]

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

[yabwon]

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

[Daily1]

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

[Daily1]

Sql Code 

Result

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

[Tom]

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?

[Kurt_Bremser]

Maxim 2: Read the Log.

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

[Daily1]

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;

[Kurt_Bremser]

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

[Daily1]

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

[Kurt_Bremser]

I already gave you the answer.

[Tom · Accepted solution]

---

@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
 )
;

[Tom]

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;