topic How to use Proc SQL to assign values from one variable to another in SAS Programming

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

mtakayesu1 — Thu, 31 Oct 2024 22:31:43 GMT

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

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

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

Tom — Thu, 31 Oct 2024 22:43:51 GMT

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;

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

mtakayesu1 — Thu, 31 Oct 2024 23:04:43 GMT

Thank you very much!