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?
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.