topic Adding New Variables in SQL in SAS Programming

Adding New Variables in SQL

Junyong — Mon, 15 Apr 2019 01:54:11 GMT

Suppose an easy data set as follows.

data _;
input i @@;
cards;
1 2 3 4 5 6 7 8 9 10
;
run;

Using SET, one can create a new indicator variable as follows.

data _;
set _;
o=mod(i,2);
run;

This can be similarly done through SQL as follows, but it spits out a warning message—WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem.

proc sql;
create table _ as
select *,mod(i,2)=0 as e
from _;
quit;

Is there any correct alternative?

Re: Adding New Variables in SQL

PGStats — Mon, 15 Apr 2019 02:34:15 GMT

To avoid problems, this operation must be done in two steps in SQL:

proc sql;
alter table _ add o real;
update _ set o = mod(i,2);
quit;

Re: Adding New Variables in SQL

ChrisNZ — Mon, 15 Apr 2019 03:26:31 GMT

Or to avoid processing the table twice:

proc sql;
  create table _TMP_TABLE_ as
  select *, mod(I,2)=0 as E
  from _;
quit;
proc datasets noprint;
  age _TMP_TABLE_ _;
quit;