I inherited a program with an existing querry that works by continually applying changes to a temp table in proc sql but generates warning messages. Here is the simplest part of that process:
proc sql; ... create table tempds as select a.*,b.wantedcol from tempds as a, othertable as b where a.fkey1=b.sfkey1 and a.key2=b.key2; quit;
an example of the warning:
WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity
problem.
I want to fix this and stay within proc sql even though I could handle this specific example in a data step since I have more sql querries to fix.
This is my attempt to fix, but I am doing something wrong between the SET statement and the FROM statement. I'm sure there is some slight syntax change I need but I cant figure out what needs updating
PROC SQL; ... ALTER TABLE tempds ADD wantedcol num(3); UPDATE tempds SET wantedcol=b.wantedcol FROM tempds a join othertable b WHERE a.fkey=b.sfkey and a.key2=b.key2; quit;
The error is the following at the FROM keyword:
ERROR 22-322: Syntax error, expecting one of the following: ;, !!, *, **, +, ',', -, /, WHERE, ||.
This is sas 9.4.
I'm using these 2 links as reference, and maybe sas's sql syntax is a bit different for the FROM keyword:
https://documentation.sas.com/doc/en/vdmmlcdc/8.1/sqlproc/n1hz0uhw57yye2n16m5r103jjpjj.htm
Thanks,
Put a SUBQUERY after the = that returns only one value.
Example:
data have;
set sashelp.class;
run;
data other;
set sashelp.class(obs=5);
keep name sex ;
rename sex=gender;
run;
proc sql;
alter table have add gender char(1);
update have a
set gender=(
select b.gender from other b
where b.name = a.name
)
where name in (select name from other)
;
quit;
proc print;
run;
Because the created table name is the same as the input table,.
You could use option UNDOPOLICY=NONE to suppress this kind of warning info.
proc sql undopolicy=none ;
..............
Put a SUBQUERY after the = that returns only one value.
Example:
data have;
set sashelp.class;
run;
data other;
set sashelp.class(obs=5);
keep name sex ;
rename sex=gender;
run;
proc sql;
alter table have add gender char(1);
update have a
set gender=(
select b.gender from other b
where b.name = a.name
)
where name in (select name from other)
;
quit;
proc print;
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.