How to use Proc SQL to assign values from one variable to another

[mtakayesu1]

Hi SAS Community,

Let's say I have one SAS dataset with one variable called ID1 with 4 values (1-4) and another variable called ID2 with 4 values (1-3 and then one missing value).

ID1   ID2

1       1

2      2

3      3

4     

I want to fill in the missing value of ID2 with the value in ID1. In data set programming I would usually say IF ID2 = '    ' then ID2 = ID1.  This will give me the value of 4 for the ID2 missing record.  This is easy in the SAS Datastep, but does anyone know how to accomplish the same result using PROC SQL?  Please note, I will have to do this over many records and we will be using SQL more often, but at this point if I can have the PROC SQL code that would help me greatly. 

I tried using CASE WHEN but it doesn't seem to look across more than one variable. 

Thanks

[Tom]

Yes, CASE will work exactly the same as your IF statement for this case.

So if in a data step you did:

data want;
  set have;
  if missing(id2) then id2=id1;
run;

Then in SQL you could do:

proc sql;
create table want as
  select id1,case when (missing(id2)) then id1 else id2 end as id2
  from have
;
quit;

But in both of them you would normally just use the COALESCE() function instead for this.  (If the ID variables are character then in a data step you would need to use COALSECEC(), but in SQL you can just use COALESCE() for either numeric or character values).

data want;
  set have;
  id2 = coalesce(id2,id1);
run;
proc sql;
create table want as
  select id1,coalesce(id2,id1) as id2
  from have
;
quit;

[mtakayesu1]

Thank you very much!